Optimize Stored Procedure I am not an extremely experienced SQL Dev.  I am worried that some of the Stored Procedures I have created for my database aren't exactly optimized, and may not be all efficient when they are running.

For example, this procedure just looks like a mess to me, but I don't know what to do to clean it up.

CREATE PROCEDURE UpdatePartInfo(
    @PartID       INT,
    @Description  VARCHAR(MAX) = NULL,
    @Information  VARCHAR(MAX) = NULL,
    @Supplier     VARCHAR(100) = NULL,
    @Manufacturer VARCHAR(100) = NULL,
    @Subcategory  VARCHAR(100) = NULL
) AS
BEGIN
    IF(EXISTS(SELECT part_id FROM parts WHERE part_id = @PartID))
    BEGIN
        BEGIN TRANSACTION UpdatePart
        BEGIN TRY
            IF @Description IS NOT NULL
                BEGIN
                    UPDATE parts
                    SET part_description = @Description
                    WHERE part_id = @PartID
                END
            IF @Information IS NOT NULL
                BEGIN
                    UPDATE parts
                    SET information = @Information
                    WHERE part_id = @PartID
                END
        END TRY
    END
    ELSE
        RETURN 1
END

The rest of the procedure is the same, checking each optional parameter to see if a value was passed, and if it was, updating the value in the table for that part.

But I feel like I should be able to construct one single Update query which does everything, instead of a whole bunch of smaller ones.

Anyway, does anyone have any good resources that could help me make some better optimized code?  At the very least some good places to go for learning better SQL so I can improve upon the knowledge I have at the moment?

Generally, for the case where NULL can only mean that the column should not be updated, the pattern is as follows:

UPDATE dbo.table
  SET col1 = CASE WHEN @param1 IS NOT NULL THEN @param1 ELSE col1 END,
      col2 = CASE WHEN @param1 IS NOT NULL THEN @param1 ELSE col2 END,
  WHERE key = @key;

As Erik points out, there may be considerations for whether it is worth testing if the value has changed before invoking an UPDATE:

The Impact of Non-Updating Updates

In my testing, I have found that it is typically not worth checking first, but I did not take into account all of the little details Paul mentioned (I ignored LOB columns, for example):

Checking if a non-LOB column needs to be updated

And you don't need a transaction around a single UPDATE. It is by definition a transaction - it will either succeed or fail.

If you want to allow the user to override an existing value with NULL, then for those columns you could potentially pass a token value that would never appear in the data naturally, like a poop emoji. For example:

UPDATE dbo.table
  SET col1 = CASE WHEN @param1 = N'' THEN NULL
                  WHEN @param1 IS NOT NULL THEN @param1
                  ELSE @param1 END,
  ...

Or you could be more complex and literal, adding an additional parameter per nullable column, like

...
@param1 nvarchar(32) = NULL,
@ForceParam1Null bit = 0,
...
  UPDATE dbo.table
    SET col1 = CASE WHEN @param1 IS NULL
                     AND @ForceParam1Null = 1 THEN NULL
                  WHEN @param1 IS NOT NULL THEN @param1
                  ELSE @param1 END,
    ...


MariaDB 5.5.47 but cannot Install TokuDB This is on CentOS 7.1,
sudo yum install MariaDB-tokudb-engine says there's no such package and if I try to start the server with plugin-load=ha_tokudb in my.cnf I get

[ERROR] Can't open shared library '/usr/lib64/mysql/plugin/ha_tokudb.so' (errno: 0, cannot open shared object file: No such file or directory)
[ERROR] Couldn't load plugins from 'ha_tokudb.so'.

in the error log (but the server starts OK)

It wasn't me who installed this MariaDB and I'm trying to enable/install TokuDB as it's best suited for the workload imo (or at least I have to try)

How do I install/enable TokuDB on this server? How to get that yum package installed for example if that's required in my case

What I did to get the built in TokuDB was 'remove' mariadb, mariadb.server and reinstall it using the steps here.

This setup even keeps the database files in a different dir rather than /var/lib and it still worked fine as long as you call mysql_upgrade --socket=[your custom socked location]


ORA-02049 and ORA-02063 Errors Select For UPDATE NOWAIT

is performed on a table by 20 processes in parallel

The error below is returned by the 4 processes out of 20 (16 processes are successful)

SQL_UPDATE_ERROR- : SQL update error ORA-02049: timeout: distributed transaction
waiting for lock ORA-02063

Are  there some parameters in Oracle, which define  how many connections can be made to a table using a database link?

Order of the Steps is Select

It's not necessarily the number of transactions, but the timeout. The parameter distrubuted_lock_timeout is set to 60 seconds by default. The purpose of this parameter is to avoid having distributed transactions in a long running wait status while something else is performing work on that row; the transaction will wait 60 seconds, then Oracle kills it.

You can modify this parameter (requires an instance restart) to whatever you want (in seconds).


DB2 LUW Timestamp differences in versions Im new to DB2, my back ground is in MS SQL server 2000 thru 2012. I have a developer using

  Local DB DB2 LUW v9.7.500.4299 FIXPACK_NUM = 5

She is selecting some data from a table where the field is a Timestamp data type. She can select it and get a return using the following WHERE CRTD_DT_TM = '2014-11-19 18:00:00'

But if she does the same thing in the Prod version:

  DB2 LUW v.9.7.0.6 FIXPACK_NUM=6

she doesn't get anything in return. She actually has to add the milliseconds to the query:

Where CRTD_DT_TM = '2015-01-13 04:32:13.080000'

Is this an issue between versions? If so Is there a FIX pack that has addressed this?

Thanks in advanced!

Depending on its precision, a TIMESTAMP value may have fractional seconds. For literals, the precision is defined by the presence of a fractional part after seconds, as well as the number of digits in the fractional part. For columns, the precision is defined as an optional parameter after the name of the type in the column definition. For instance:

SomeColumn TIMESTAMP(9)

defines the column as a timestamp with nanosecond precision (9 digits after the decimal point). (As you have SQL Server background, you can compare this to the SQL Server datetime2 type, which also has optional precision.)

For reference, see IBM Knowledge Center - Datetime values.

It looks likely that the column in question is defined differently between the development environment and production, the development definition being TIMESTAMP(0) (no fractional seconds) and the production definition TIMESTAMP(6) (or simply TIMESTAMP, which means the same).

That explains why your developer never has to specify fractions of a second in the development environment – because the column's values cannot possibly have fractional seconds. At the same time, she may have to specify timestamps with a fractional part in the production environment because the column type there allows fractions of a second in the values and some timestamps apparently do have fractional parts.


Can't get users default schema to change - SQL Server 2008 We are migrating from a SQL Server 2008 instance on a shared host to our own box. Our issue is we have a bunch of tables, procs and views that are prefaced with a schema (let's call the schema 'me') other than dbo.

So:

me.Employees
me.vw_EmployeesStuff
me.proc_SaveEmployee

We're having issues now that we have restored the backups from the shared host to our new box.  When logged in as 'me', we need the queries to run like this:

SELECT * FROM vw_EmployeesStuff

And not have to query like:

SELECT * FROM me.vw_EmployeesStuff

We've tried to change the default schema for the user from dbo to me, but it doesn't seem to be 'taking'. Any thoughts or help is much appreciated. TIA

You have to migrate all the users from the original instance using the procedure sp_help_revlogin.


user/login name on process unknown SQL Server version is 11.0.5569.0.

The user/login for the process on a server is as follows:

S-1-9-3-412646706-1242810017-220819094-3312857753

This is not an orphaned user (as suggested below) because I checked that before entering this post.

I looked at the items running using Ideradm, sp_blitzfirst, and sp_whoisactive.  The explain plan indicates that this is working with a couple of temp tables that it obviously created in a prior step.

When I ran a script against the 'principals' tables, NADA, nothing.

Any other suggestions?

EXECUTE AS USER = 'SomeUser'; where 'SomeUser' is a user who doesn't have a linked login at the server level will result in SQL Server reporting the login name as a SID starting with s-1-9-3.

You can use the original_login_name column in sys.dm_exec_sessions to determine who or what is impersonating the user without a login; for instance:

SELECT SessionID = des.session_id
    , LoginName = des.login_name
    , OriginalLoginName = des.original_login_name
FROM sys.dm_exec_sessions des
WHERE des.is_user_process = 1;

I wrote a blog-post over at SQL Server Science showing this behavior, and how to identify which database-level-principal is being impersonated.


Actual Row Size vs My Estimated Row Size I have a table of 500 million rows on SQL Server 2016. I calculate that each row should take up 12 bytes, total size should be in the order of 6 GiB. However the actual size is over 10 GiB.

After running dbcc showcontig ('[Table]') with tableresults I see the the MinimumRecordSize and MaximumRecordSize is 19 bytes and the AverageFreeBytes is 11.023 bytes.

The table is defined as the following.

CREATE TABLE [dbo].[Table](
    [Key] [binary](7) NOT NULL,
    [Value] [binary](5) NOT NULL
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-Key] ON [dbo].[Table]
(
    [Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

My question is where is the extra 7 bytes per row coming from? And is there anything I can do about it?

Also if there are any suggestions about space optimizations would also be appreciated, the table will be read-only (no updates or inserts).

Michael pointed you to the source (Paul Randal's "Anatomy of a Page" and "Anatomy of a Record") in an above comment, but to summarise:

SQL Server data is stored in 8KB pages, each of which contains a 96-byte header, and some room at the end of the record for the slot array (which tells the engine the logical order of rows in the page).
Because you use a clustered index, the b-tree structure must also be taken into account, per Dan's comment.

All this considered, we can make the following observation:

All things being equal, at 12 bytes per row, we would be able to fit 674 rows in a single 8KB data page. That works out to be 741,840 pages in total, for 500 million rows. No doubt, this is where your 6GB figure comes from, or more specifically, 6,077,153,280 bytes.

However, there's an overhead per row, as you discovered. Summarising from Paul's Anatomy of a Record:

Two bytes of record metadata (record type) = 2
Two bytes pointing forward in the record to the null bitmap = 2
Fixed length portion for the data itself = 12
Null Bitmap: Two bytes for count of columns in the record = 2
Null Bitmap: Variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not = 2 bits

That's 2 + 2 + 12 + 2 + 1. Yes, it works out to 7 bytes as you calculated, but it could easily be more if there are more columns in the table.

Assuming then a length of 19 bytes per row, you can fit 426 rows in a page. That works out to 1,173,709 pages, or 9,615,024,128 bytes.

If we have a b-tree structure with 500,000,000 rows, your 1,173,709 pages will be at the leaf level. You'll need roughly 2,500 pages for the intermediate level (I don't know the exact length of the intermediate index record off the top of my head, so I guessed 16 bytes), which is an additional 200MB, give or take, and you may have two intermediate levels between root and leaf level.

With this in mind, based on my terrible estimates for the b-tree structure, that would take you to around the 10GB range you see for your data.

(All credit to Dan Guzman and Michael Green for their comments, and of course Paul Randal for his blog post.)


12c Grid Agent installation issues - Shell path is incorrect or not defined I am installing 12c Grid Control agent on my server via Enterprise manager.

The targer server is a UNIX server with AIX OS on it. I add target host manually, enter authentication information, host name, installation directory, etc. and all goes well untill I click "Deploy Agent" button, then I get the following error:

"Shell path is incorrect or not defined.:/bin/bash(SH_PATH),-c(SH_ARGS) on host MyHostName"

and details are :

"Check the property values in the following files in this order, ssPaths_.properties or sPaths.properties or Paths.properties, in "/u02/app/oracle/middleware/oms/oui/prov/resources"

There isn't /u02/app" directory on my target server, only /u01/app/oracle" so I assume the above directory is on the same server as Enterprise manager.

Although I did find a directory where I have files of the format  ssPaths_.properties  - /u01/app/oracle/product/11.2.0.3/oui/prov/resources

Since my platform ia AIX, I used file   ssPaths_aix.properties

On my target server if I run which bash I get /usr/bin/bash and which ssh returns /usr/bin/ssh ahd which sh returns /usr/bin/sh

So I edited the ssPaths_aix.properties file so that SH_PATH=/usr/bin/sh  but still getting the same error. I have also tried setting to SH_PATH=/usr/bin/bash but nothing seems to make any difference. I am thinking one possibility would be I am editing wrong file.

Can anyone help?

try the following doc
EM 12c Agent Push Fails With Shell Path is Incorrect or Not Defined : /bin/bash(SH_PATH),-c(SH_ARGS) (Doc ID 1489437.1)


T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER I have a table with below structure :

FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int)

based on this table I wrote this query

       SELECT
          S.OPRDATE ,
          S.CFCIFNO ,
          SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
          SUM(CASE WHEN S.SATNAPAYATYPE=2 AND
                        S.OPRCOD=0 AND
                        S.voucherno<>-1 THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,

          COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
          COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND
                          S.OPRCOD=0 AND
                          S.voucherno<>-1 THEN 1 ELSE NULL END)   AS SATNA_WITHDRAW_COUNT
   from factsatnaevent s
   where s.cfcifno<>-1
   GROUP BY S.OPRDATE ,
            S.CFCIFNO ;

I Just want to know if there are better way to write this query .
I need to have this output

OPRDATE   CFCIFNO    SATNA_DEPOSIT_AMNT    SATNA_WITHDRAW_AMNT     SATNA_DEPOSIT_COUNT   SATNA_WITHDRAW_COUNT

Please tell if any further information is required. Thanks in advance

Avoid <> in where condition.Instead use >-1
When you are already filtering anything like  where s.cfcifno<>-1  ,then no need of putting same in case condition because that condition is already filtered.

Your query seem ok to me,

SELECT
          S.OPRDATE ,
          S.CFCIFNO ,
          SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
          SUM(CASE WHEN S.SATNAPAYATYPE=2 AND
                        S.OPRCOD=0
                        THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,

          COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
          COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND
                          S.OPRCOD=0
                          THEN 1 ELSE NULL END)   AS SATNA_WITHDRAW_COUNT
   from factsatnaevent s
   where s.cfcifno>-1
   GROUP BY S.OPRDATE ,
            S.CFCIFNO ;

Are you facing any performance issue ?


"Login failed for user" when using admin user in SSMS I am trying to connect to an Azure SQL database via SSMS. The connection string supplied by Azure is:

  Server=tcp:SERVER_NAME.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

So, first of all I'm not sure if I should include ALL of

tcp:SERVER_NAME.database.windows.net,1433

in the server name when connecting via SSMS.   Also, the username is an email address.  Let's say it's:

foo@bar.com

So, this is what I tried:

ServerName: SERVER_NAME.database.windows.net
Login:      foo@bar.com@SERVER_NAME.database.windows.net
Password:   [password]

At first when I tried, I was told that the IP was not allowed.  So I added the IP to the server firewall settings.  But now, I get this:

  Login failed for user foo@bar.com

I am using the same UserID and Password that I use to log into the Azure portal.  And it's the UserID that is specified in the Access Control window.

I am working from home, but I have also tried while on my company's VPN.  Neither work.

Is there something else I'm doing wrong?

Pretty sure the problem is in the username. When you connect to an Azure SQL Database instance, foo@bar implies that you're connecting as the login "foo" to the instance "bar.database.windows.net".

The credentials you use when logging into the Azure management portal won't work when you're using SSMS.

Verify that you've set up a SQL Database login, and use that login. The admin account is set up in the Azure portal - you can verify the login name by clicking "Properties" on the server instance.

Regular logins/users are set up as 1) logins on the database server, or 2) as contained users in each database (if you have enabled containment), or 3) in Active Directory (if you've configured one).


Are there any downsides to referencing tables from another schema using synonyms? We are developing a modular system composed by an administration module and a few web apps. The database model is also split in that way: we have a schema for the admin module and another for each application. For example:

Users will be shared across the system (you register and are given access to one or more applications), so the Users table is in the admin schema.
Application ACME will generate reports and store them, so the ACME Reports table is in the ACME schema.

Simple enough, except ACME reports are associated with a user. We could simply have copies of the Users table in each schema, and update them each time the "users master table" (admin schema) is changed, but that would be extremely painful to work with. Instead, we are sharing the table, and creating synonyms to abstract the code from that fact.
So, in our admin schema:
create table admin_users ( ... );
grant select, references on admin_users to acme;

And in our ACME schema:
create synonym acme_users for admin.admin_users;
alter table acme_reports add constraint fk_user foreign key (user_id)
      references acme_users (id);

This works just fine and will keep the referential integrity across schemas, but are there any downfalls to this approach we should be aware of, or any best practices we should follow here? With regards to either accessing tables from another schema, or using the (private) synonyms.
All the schemas will be on the same Oracle database, so there shouldn't be any significant performance impact (compared to having all the tables in the same schema). Right?
If this approach is valid, is there any practical way to manage the grants? This works:
grant select, references on admin_table to app_schema

But the number of tables which need to be granted will grow over time, and so will the number of schemas. Whenever we add a new schema, we would have to grant it the permissions for all the shared tables.
We thought of granting the permissions to a role instead, and just giving that role to the new schemas. But apparently that doesn't work:
ORA-01931: cannot grant REFERENCES to a role

Is there any other way to manage it?

There is no performance issue in having an app access tables from multiple schemas.  You are correct in that you do not want to duplicate data across multiple schemas.  As for managing the privileges, grant the privs to a role, not the individual user.  Then grant the role to the user(s).  When new objects are created, grant the appropriate privs to the role, and any user to which the role was previously granted will pick up the new privs in any future sessions they create.


Column does not exist in UNION ALL query I am doing some code for my badminton club.

Tables

CREATE TABLE School (
    Name text PRIMARY KEY
);

CREATE TABLE Tournament (
    Number integer PRIMARY KEY
);

CREATE TABLE Player (
    Name text NOT NULL,
    SchoolName text NOT NULL,
    Gender text CHECK (Gender IN ('Masculin', 'Féminin'))
);

ALTER TABLE ONLY Player
    ADD CONSTRAINT Player_pkey PRIMARY KEY (name, SchoolName);

ALTER TABLE ONLY Player
    ADD CONSTRAINT SchoolName_fkey FOREIGN KEY (SchoolName) REFERENCES School(Name);

CREATE TABLE Result (
    -- PlayerId_fkey text
    PlayerName_fkey text,
    SchoolName_fkey text,
    Category text CHECK (Category IN ('Benjamin', 'Cadet', 'Juvénile')),
    TournamentNumber integer,
    Score integer,
    TypeOfPlay text CHECK (TypeOfPlay IN ('Simple', 'Double'))
);

ALTER TABLE ONLY Result
    ADD CONSTRAINT PlayerName_fkey FOREIGN KEY (PlayerName_fkey, SchoolName_fkey) REFERENCES Player(Name, SchoolName);

ALTER TABLE ONLY Result
    ADD CONSTRAINT TournamentNumber_fkey FOREIGN KEY (TournamentNumber) REFERENCES Tournament(Number);

Query

-- Select each player with their single and double total score
SELECT PlayerName_fkey, ScoreSimple, ScoreDouble

FROM (
    -- Select the sum of the best results for each player in single play
    SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreSimple
    FROM (
        -- Select the best four results in the season for each player in single play
        SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
        FROM (
            -- Select, for each player, the score for each tournament they played in single play
            SELECT PlayerName_fkey, score, TournamentNumber
                 FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                 WHERE Category='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score > 0 ) tBestIndividualScoresWithDuplicates
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank

    UNION ALL

    -- Select the sum of the best results for each player in double play
    SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble
    FROM (
        -- Select the best four results in the season for each player in double play
        SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
        FROM (
            -- Select, for each player, the score for each tournament they played in double play
            SELECT PlayerName_fkey, score, TournamentNumber
                 FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                 WHERE Category='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Double' AND score > 0 ) tBestIndividualScoresWithDuplicates
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank) t1

Question

The problem I have is I get the error: "column "scoredouble" does not exist". How do I get the scoredouble column display on my first query? I cannot use a JOIN because some players have scores in one type of play (singles or double) and not the other.

Link here: http://rextester.com/XIG14273

With the help of many people whom gave me a lot of help, I finally got a working solution:

SELECT PlayerName_fkey, SumScoresSimpleTotal, SumScoresDoubleTotal, SumScoresTotal, rank() over (order by SumScoresTotal desc) AS OverallRank

FROM (

    SELECT COALESCE(t1.PlayerName_fkey, t2.PlayerName_fkey) as PlayerName_fkey, coalesce(SumScoresSimple, 0) as SumScoresSimpleTotal, coalesce(SumScoresDouble,0) as SumScoresDoubleTotal,
    (coalesce(SumScoresSimple, 0) + coalesce(SumScoresDouble, 0)) as SumScoresTotal
    FROM (

    SELECT PlayerName_fkey, ScoreSimple, sum(ScoreSimple) over (partition by PlayerName_fkey) as SumScoresSimple

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreSimple
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score > 0 ) tBestIndividualScoresWithDuplicates
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank)tinside1) t1

        FULL OUTER JOIN

    (SELECT PlayerName_fkey, ScoreDouble, sum(ScoreDouble) over (partition by PlayerName_fkey) as SumScoresDouble

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Double' AND score > 0 ) tBestIndividualScoresWithDuplicates
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank) tinside2) t2

    ON t1.PlayerName_fkey = t2.PlayerName_fkey

    GROUP BY t1.PlayerName_fkey, t2.PlayerName_fkey, SumScoresSimple, SumScoresDouble

    ORDER BY SumScoresTotal DESC

The colasesce functions are particularly important here to avoid empty entries for both the player names and the scores.


Reading a SQL Server Execution plan I am very new to any type of tuning but have been learning more.. One of my specific databases one of the problems identified with slow queries are a number of Implicit conversions. It is my understand that when 2 non like column types are compared SQL Server has to convert one to the other, there is listing of priorities that tell it how to convert - Since the data type is converted, its possible that it will not use indexes correctly.. One of the first queries identified is a very basic one, and I would like to see if someone could help me identify what I'm seeing on my execution plan...

The basic query is:

(
@1 TINYINT
,@2 VARCHAR(8000)
,@3 NUMERIC(4, 0)
)
SELECT CONVERT([float], [low] / @3, 0)
FROM [master].[dbo].[spt_values]
WHERE [number] = @1
AND [type] = @2

The Execution plan shows:

Detail on the clustered index seek

spt_vaules.Type is Defined as nchar(3)

The Seek Predicates section is confusing me, exactly which part of this is it converting... My query says [low] is being converted from int to numeric but that doesn't seem to be what the cluster index seek details is showing, is it?

Is there a way to tell in this specific example how much "Better" it would be if I did the conversions Explicitly?  I would think I could just do a "cast" in the query and insert a few numbers where the variables are, is that correct?

Why don't your input parameters match the type for the table? Why would you want to keep the wrong types there and perform any casts or conversions at all (whether implicit or explicit)? Why are you converting anything to FLOAT, of all things? To address specific questions:

  My query says [low] is being converted from int to numeric but that doesn't seem to be what the cluster index seek details is showing, is it?

The convert of low is happening in the output, not in the seek predicate (the predicate is what is used to find matching rows and/or eliminate non-matching rows).

  Is there a way to tell in this specific example how much "Better" it would be if I did the conversions Explicitly? I would think I could just do a "cast" in the query and insert a few numbers where the variables are, is that correct?

There's no way to make the execution plan show you how much better a different plan would be, except to generate that different plan and compare. You can use this comparison to document how much better it would be if the interface were correct (and two other ways would be to keep the interface but (a) perform explicit converts in the query - not of the column, but of the variables or (b) use local variables of the right type and assign them the values of the parameters). So you could show them 3 different ways to solve the problem, and show evidence that all 3 are better than the current version.

My recommendation is to fix the procedure the right way. First let's look at the actual types you care about:

USE master;
GO
SELECT t.name, c.max_length/CASE
  WHEN t.name LIKE N'n[cvt]%' THEN 2 ELSE 1 END
FROM sys.all_columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.system_type_id = t.user_type_id
WHERE EXISTS
(
  SELECT 1 FROM sys.all_objects AS o
    INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
    WHERE o.[object_id] = c.[object_id]
    AND o.name = N'spt_values'
    AND s.name = N'dbo'
)
AND c.name IN (N'number',N'type');

Results:

number    int     4
type      nchar   3

So the interface to your stored procedure should be:

USE yourdb;
GO
ALTER PROCEDURE dbo.some_name
  @1 INT,
  @2 NCHAR(3),
  @3 NUMERIC(4, 0)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT CONVERT([float], [low] / @3, 0) -- don't think you want float here
    FROM [master].[dbo].[spt_values]
    WHERE [number] = @1
    AND [type] = @2;
END
GO

Implicit conversions between varchar and nvarchar can be particularly bad (especially in the opposite scenario as yours - parameter is nvarchar and column is varchar), but there really is no reason to allow for a 8000-character parameter of any type when the longest string possible in the table is 3 characters...


Cannot Generate SSPI Context when connecting to the remote Computer I am connecting to Windows Server 2008 R2 which has SQL server 2005 installed via ODBC from a client machine. Recently I started getting this error

  Cannot generate SSPI Context.

I have gone through similar questions but have not got any helpful answer.

There is a problem with the SPN of the SQL Server. Look at the Microsoft Kerberos Configuration Manager tool.
https://www.microsoft.com/en-us/download/details.aspx?id=39046

This will look at the SQL Server and tell you what the SPN issue is with the machine and provide you a script to resolve. The script will need to be run by someone with Domain Admin permissions. This tool essentially automates the manual steps found in this article.
https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message


Index isn't used when value is a result of a subquery or from a wrapper query I have a simple one-to-many relationship: accounts and events where an account may have many events.
accounts

id: uuid
...more irrelevant fields

events

id: serial
type: enum ['activated', 'deactivated', 'removed', ...more]
account_id: uuid
created_at: creation date of the row

events table index:
create index "IDX_d82e2f903c778153e7781f455e" on bank_account_events (type, bank_account_id);

I have a simple query which determines when the last event of a type occurred for a specific account:
explain select created_at
from bank_account_events
where bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'
  and type in ('activated', 'deactivated', 'removed')
order by id desc
limit 1;

The query's result shows that we use the correct index is being used:
Limit  (cost=339.51..339.51 rows=1 width=16)
  ->  Sort  (cost=339.51..339.74 rows=94 width=16)
        Sort Key: id DESC
"        ->  Index Scan using ""IDX_d82e2f903c778153e7781f455e"" on bank_account_events  (cost=0.56..339.04 rows=94 width=16)"
"              Index Cond: ((type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])) AND (bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid))

However, when I "wrap" this query and use it as a subquery the index isn't being used:
explain select ba.id,
  (
    select created_at
    from bank_account_events
    where bank_account_id = ba.id
      and type in ('activated', 'deactivated', 'removed')
    order by id desc
    limit 1
  ) status_date
from bank_accounts ba
where ba.id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964';

The result of the query above is:
Index Only Scan using ""PK_5a7a02c20412299d198e097a8fe"" on bank_accounts ba  (cost=0.27..8.43 rows=1 width=24)"
  Index Cond: (id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid)
  SubPlan 1
    ->  Limit  (cost=0.43..4.14 rows=1 width=16)
"          ->  Index Scan Backward using ""PK_dddc8f2295ddc2561044644a05a"" on bank_account_events  (cost=0.43..264940.10 rows=71513 width=16)"
"                Filter: ((bank_account_id = ba.id) AND (type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])))

As you can see, it's using now the PK of the table (PK_dddc8f2295ddc2561044644a05a) which is just the serial id. Then it filters the results. Since the table has a few million rows, this is painfully slow.
Why isn't Postgres using the index in this scenario? The only change was that instead of referencing the id directly, we refer to it as ba.id.

Change the order of the columns in the index to support the B-tree search for the bank_account_id and filtering by type. Once filtered, all records are read ordered by id. By including the created_at column last, you have all the data the query needs in the index and it won't access the table at all.
CREATE INDEX "nc_bank_account_events_bank_account_id"
ON bank_account_events (bank_account_id, type, id, created_at);


How, when and why does implicit changing of transaction isolation level happen? RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server.

This is, as I understand, the context of the sripts in MSDN blog article "Concurrency Series: Minimizing blocking between updaters" telling that:

"The second trick of using RCSI does not work either and the update gets blocked behind X lock by session-1 on row with (C1 = 1). The reason is that the following UPDATE statement executes at a higher isolation level than read committed.

update basic_locking set c2 = 1000 where c1 = 2

Here is the output showing the blocking

Solutions: Here are two choices to address this issue:"

How does "the output" illustrate the implicit elevation of implicit default isolation level (of Read Committed)?

How to check for real isolation level "jumpings" in context of some statements?

When to expect them and why do they occur?

How does the output illustrate implicit elevation of isolation level?

Sunil is technically correct, but it does sound a little confusing, I agree.
The output shows the session is blocked waiting to acquire a U lock. The definition of the READ COMMITTED isolation level is that the session will only encounter committed data. SQL Server honours this logical requirement under the default pessimistic (locking) implementation of read committed by holding shared locks just long enough to avoid seeing uncommitted data. These shared locks are normally quickly released (usually just before reading the next row).
Under optimistic (row-versioning) read committed (RCSI) SQL Server avoids reading uncommitted data by reading the last-committed version of the row at the time the statement started instead.
The sense Sunil is trying to convey is that taking U locks (instead of brief shared locks or reading versions) represents a (technical) escalation of isolation level (though not to any explicitly named level).
The effective isolation level in this case is not quite REPEATABLE READ because any U locks taken (and not converted to X locks) are released at the end of the statement. This is different from the behaviour of the UPDLOCK hint, which acquires and holds U locks (at least) until the end of the transaction. In addition, REPEATABLE READ generally acquires S locks (though this is strictly just an implementation detail).
Confusingly, the engine also takes U locks on the access method when identifying rows to update under default (locking) read-committed. This is a convenience to avoid a common deadlocking scenario without having to specify UPDLOCK explicitly. I apologise that this is so complicated, but there we are.

How to check for real isolation level "jumpings" in context of some statements?

There is nothing explicitly exposed in query plans to identify cases where the engine temporarily increases the effective isolation level. This might change in a future version of SQL Server. There may be indirect evidence in terms of locks taken, but this is rarely a convenient approach.

When to expect them and why do they occur?

Some of the occasions when internal escalation occurs are (somewhat) documented in Books Online. For example, Understanding Row Versioning-Based Isolation Levels says (among other things worth noting):

In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read.

The general reason for temporary changes in effective isolation level changes is to avoid data corruption. A list of posts identifying some common cases follows:
Blocking Operators
Large Objects
Lookup with Prefetching
Cascading Referential Integrity
Other common cases (not a complete list):

Shared locks taken when the query processor verifies foreign key relationships.
Range locks taken when maintaining an indexed view referencing more than one table.
Range locks taken when maintaining an index with IGNORE_DUP_KEY.

Some of these behaviours may be documented in Books Online, somewhere, but there's no convenient single list that I am aware of.


Can't get this PostgreSQL query to run faster I'm running PostgreSQL 9.6 with PostGIS 2.3.3

I'm trying to make this rather critical query go faster (looking for users within 1000 meters of a location), but I'm having troubles setting up the right indexes.

Could someone point me in the right direction?

users are 200k rows, locations are 1200 rows, user_push_tokens are 155k rows

users table:

create table users
(
  id serial not null
    constraint users_pkey
      primary key,
  (an additional 20-ish columns),
  geo_point geometry(Point,4326)
);`

create unique index users_id
  on users (id);

create index users_geo_point_idx
  on users using gist(geo_point);

user_push_tokens table:

create table user_push_tokens
(
  user_id integer not null
    constraint push_tokens_user_id_fkey
      references users
        on delete cascade,
  push_token varchar(255) not null,
  push_provider varchar(64) not null,
  app varchar(64) default 'app'::character varying not null,
  id integer default nextval('user_push_tokens_id_seq'::regclass) not null
    constraint user_push_tokens_pkey
      primary key,
  active boolean default true not null,
);

create index trinity_unique_index
  on user_push_tokens (user_id, app, push_token);

create index user_push_tokens_token_fetch_idx
  on user_push_tokens (user_id, app, active);

create index user_id_index
  on user_push_tokens (user_id);

locations table:

create table locations
(
  id integer default nextval('locations_id_seq'::regclass) not null
    constraint locations_pkey
      primary key,
  (another 25 columns),
  geo_point geometry(Point,4326)
);

create unique index locations_id_key
  on locations (id);

create index locations_geo_point_idx
  on locations using gist(geo_point);

The query

EXPLAIN ANALYSE SELECT
    u.id AS user_id,
    upt.push_provider,
    upt.push_token
  FROM users u
    JOIN locations l ON l.id = 3896
    JOIN user_push_tokens upt
      ON upt.user_id = u.id AND upt.active = true AND upt.app = 'app'
  WHERE ST_DistanceSphere(u.geo_point, l.geo_point) <= 1000;

results in

Nested Loop  (cost=26087.06..63658.87 rows=30605 width=107) (actual time=353.304..887.371 rows=2498 loops=1)
  Join Filter: (_st_distance(geography(u.geo_point), geography(l.geo_point), '0'::double precision, false) <= '1000'::double precision)
  Rows Removed by Join Filter: 89539
  ->  Index Scan using locations_id_key on locations l  (cost=0.28..8.29 rows=1 width=32) (actual time=0.009..0.014 rows=1 loops=1)
        Index Cond: (id = 3896)
  ->  Hash Join  (cost=26086.78..39090.07 rows=91815 width=139) (actual time=352.437..657.228 rows=92037 loops=1)
        Hash Cond: (upt.user_id = u.id)
        ->  Seq Scan on user_push_tokens upt  (cost=0.00..7162.82 rows=91815 width=107) (actual time=0.032..103.512 rows=92037 loops=1)
              Filter: (active AND ((app)::text = 'app'::text))
              Rows Removed by Filter: 62437
        ->  Hash  (cost=22114.46..22114.46 rows=195546 width=36) (actual time=352.199..352.199 rows=195589 loops=1)
              Buckets: 65536  Batches: 4  Memory Usage: 3563kB
              ->  Seq Scan on users u  (cost=0.00..22114.46 rows=195546 width=36) (actual time=0.014..214.976 rows=195589 loops=1)

More readable explain output

Thank you for your time.

ST_DWithin

ST_DistanceSphere(x,y)<t cannot use your spatial index. ST_DWithin can, depending on the selectivity.  Instead use,

ST_DWithin(u.geo_point, l.geo_point, 1000);

Also, for testing purposes, you should remove the join to user_push_tokens and add it back once you get a better plan for the simpler query.


International Components for Unicode (ICU) Date Formatter for MySQL? Regarding to ICU - International Components for Unicode definition:

  ICU is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software applications. ICU is widely portable and gives applications the same results on all platforms and between C/C++ and Java software.

one of important part is formatting:

  Formatting: Format numbers, dates, times and currency amounts according the conventions of a chosen locale. This includes translating month and day names into the selected language, choosing appropriate abbreviations, ordering fields correctly, etc. This data also comes from the Common Locale Data Repository.

which implemented in vary languages; I would like to know if there is any implementation or work for MySQL to add this feature for Unicode date formatting?

I think the answer is...  Use MySQL to store dates/datetimes/timestamps in the limited formats that it provides, then use app code -- possibly some library in your app language -- to get at fancier formatting.

As a general rule, a database is responsible for storing; the application is responsible for formatting.

See also https://dev.mysql.com/doc/refman/5.7/en/locale-support.html


Clearing out [model] db On SQL Server 2012, we have been using the [model] DB so that we can easily create project DBs with all the objects (schemas, tables, stored procedures, user-defined types) already set up with a CREATE DATABASE statement.

However, if we want to clear all of this out and start over, is there an easy way to do this? Naturally we can't DROP it.

If you took a backup prior to adding objects to it you can just restore the backup. If you do not have a backup to restore from, you could take a backup of one that is empty and use that as your restore point.

Alternatively - you could delete all of the objects that you created within it. Check out some of the answers to this question for ideas around scripting this.

Failing all of the above, you might have to rebuild your system databases.


Same session ID(with same SQL text) but 3 different connection ids I was listing out all sessions open in my sql server 2016 production server.
I have issues with slow performance and i have been investigating it.
I see that i have several same sessions(session_id) with multiple connection_ids.
All are in suspended state(must be waiting for some resource..investigating this also)  and the sql text also is same.

I wonder why i have 3 different connection_ids for the same session_id.

When i looked at the sys.dm_exec_connections for those connection ids i see the below info.

Can someone help me to understand this.?

Additional Info:

That query runs in parallel.So if this indicates the parallel execution then i have the below scenarios where:

The scalar function is executing with three diff connection_ids and with same session_id.That function is not touching any tables as it takes a value are format(does substring) it to display in a different way.There is no way that the particular function can run in parallel as  per my understanding.
As shown in the screeshot ,the return statement from the same function also have 3 different connection_ids.
I have a MAXDOP of 8 set,so if this indicated parallel execution,then the number can be greater than 3 as per my understanding.

The clue here is that those connections each share a "parent_connection_id," which per the docs:

  Identifies the primary connection that the MARS session is using

I noticed the aliases used in those queries heavily implies your app is using Entity Framework.  Default connection settings (scaffolded out by the Visual Studio project templates) will enable Multiple Active Result Sets (MARS) in the Entity Framework application's connection string.

So this is normal behavior of MARS - where multiple queries will share a "parent connection" (and thus share a session), interleaving the queries submitted to it by the application.

The most common reason cited for turning on MARS with EF is to help facilitate lazy loading - which is probably why all of the queries look similar (it'll be pulling data in by ID as needed).

As far as performance problems related to this, it's hard to know without seeing the queries and code.  With the MARS and EF stuff, I'd be tempted to guess that it's the N+1 query problem, which I've discussed on my blog.  The solution is to change the .NET code so that it's not executing a query for each row of an already-retrieved resultset.


Archiving data in SQL Server with stored procedure Database in SQL Server 2014 has one table and is consuming around 17-20 GB of disk space each day. Assuming that level of usage remains fairly consistent  and not accounting for lower usage on weekends/holidays.

That would mean I am looking at requiring around 7.3 TB of space to house 1 year of table data. I want to figure out long term strategy is going to be for this, including archiving (when/where/how). The database currently contains just a tad over 1 billion rows.

I am trying to write a stored procedure to archive data in other table but I can do it once with my code. Later if I want I have to create one more table each time.

create PROC [dbo].[ArchiveData_auditlog1]
(
    @CutOffDate datetime = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    IF @CutOffDate IS NULL
    BEGIN
        SET @CutOffDate = DATEADD(mm, -8, CURRENT_TIMESTAMP)
    END
    ELSE
    BEGIN
        IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
        BEGIN
            RAISERROR ('Cannot delete orders from last three months', 16, 1)
            RETURN -1
        END
    END

    BEGIN TRAN

                SELECT * into  [dbo].[archive_auditlog2]

        FROM [dbo].[AuditLog_test]
        WHERE timestamp < @CutOffDate

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occured while copying data to dbo.archive.account', 16, 1)
            RETURN -1
        END

        DELETE [dbo].[AuditLog_test]
        WHERE timestamp < @CutOffDate

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occured while deleting data from dbo.archive.account', 16, 1)
            RETURN -1
        END

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRAN
        RETURN 0
    END
end

My question is what will be best way to fix problem, table has no dependency on other tables and has primary key on Id. I want to archive data based on timestamp column.

If I have to do partitioning how can I partition this big table, how can I migrate data to archive server.

What do you mean by archive? Do you want to delete the data?

Moving it to another db or server will still take up the same space. It may improve performance but you're just moving the problem around.

Do you need the data in its original form? Can you aggregate the data to reduce the number of rows?

If you do move the data to another table you may find a clustered columnstore may give better compression and save disk space.

Personally i would be using ssis and not  a sp using linked servers. As you have a primary key id be using that as a way of identifying what has been inserted and then can be deleted from the original table. An index on the column you are using for date range would be extremely beneficial. Id also consider using smaller batches such as one days worth at a time.


Does SQL Server read all of a COALESCE function even if the first argument is not NULL? I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:

SELECT COALESCE(c.FirstName
                ,(SELECT TOP 1 b.FirstName
                  FROM TableA a
                  JOIN TableB b ON .....)
                )

If, for example, c.FirstName = 'John', would SQL Server still run the sub-query?

I know with the VB.NET IIF() function, if the second argument is True, the code still reads the third argument (even though it won't be used).

Nope.  Here's a simple test:

SELECT COALESCE(1, (SELECT 1/0)) -- runs fine
SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error

If the second condition is evaluated, an exception is thrown for divide-by-zero.

Per the MSDN Documentation this is related to how COALESCE is viewed by the interpreter - it's just an easy way to write a CASE statement.

CASE is well known to be one of the only functions in SQL Server that (mostly) reliably short circuits.

There are some exceptions when comparing to scalar variables and aggregations as shown by Aaron Bertrand in another answer here (and this would apply both to CASE and COALESCE):

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

will generate a division by zero error.

This should be considered a bug, and as a rule COALESCE will parse from left to right.


Is this the correct way to model a relationship between an object which may or may not have another object? I'm creating a database for a department store and the database has two tables: Items and Coupons. An item, like a TV, can only ever have one coupon. Some items don't have a coupon at a given time. Therefore my schema is:

Items: item_id, name, price, desc, ...
Coupons: coupon_id, item_id, rate, ...

This way, when I query for items, I get a list of all items. If I want only items with a coupon, I can either select all coupons and then individually query for the item to which a coupon belongs, or I can perform a natural join between the two tables and get a list of only items with coupons.

I don't think adding a coupon_id column to the Items table is correct because then there will be null coupon_ids, which I believe is not optimal.

Is everything I've said above correct? Or would adding the coupon_id column to Items and having null coupon_id be completely acceptable?

I would favor your first arrangement where you have an Item_Id FK in the Coupon table.

You will be able to enforce the "One coupon per item rule" through a unique constraint on the Item_Id column in your Coupon table.
You will be able to easily allow multiple coupons per item if business decides to change their mind.
Like you said you can easily select and filter your items vs. coupons relationships
Putting the Item_Id in the Coupon table allows you to maintain a history of coupons that were valid for that item which is often useful.

The only potential problem I see is in some ways you are focusing on tying a coupon to a specific item on issuance of the coupon.  This only becomes a problem when business decides that they want to hand out "20% off your highest priced item," or "50% off everything in the store on XYZ date" coupons to their most valuable customers.  This can be resolved of course.  I just mentioned it to get you thinking about ways the design might need to change in the future.


Will MySQL be effective when selecting a row from a table with billions of records? Note: this is a hypothetical scenario

Lets say that:

I'm running MySQL, InnoDB engine, UTF-8 encoding
I have a table with 50+ billions of rows
The tables primary key is a BIGINT
The table also has 4 other columns, 3 of which are indexed (a BIGINT, TINYINT, and a VARCHAR(20)) ... (the unindexed column is type TEXT)

My questions are:

How fast would the queries be when selecting a row via primary key, or via indexed columns? What kind of hardware/server/s would be needed in order to make these queries execute faster than 0.1s?
Should I be using some other storage engine for this?

Let's do a hypothetical answer to a hypothetical question (all of this are ball-park calculations, I do not intend to be very exact, but to have a big picture):

Your table, at the bare minimum, will have a storage requirements of:

50 000 000 000*(8 + 8 + 1 + 1 + 20*3) / 1024^4 ~ 4TB

I do not have into account the TEXT because, even if it can be inline, if it is too big it goes off-page (so funnily enough, it won't be a problem or a performance penalty unless it is selected or modified).

The secondary indexes (I do not have into account the primary key, as the data is clustered around it, but obviously, it will take even more space than just the data) will take at least:

50 000 000 000 * ( (8 + 8) + (8 + 1) + (8 + 1 + 20 *3) ) ~ 4TB

And that is being too optimistic.

As that cannot be realistically into the main memory (largest deployments of traditional SMP architectures can only integrate around 4TB, I do not consider other architectures with its own problems), a single access though primary key may require ~25 theoretical disk hits (double it for secondary keys), which with fast enough disks (a huge RAID of SSDs) and disabling large read-ahead caching to the buffer pool and other tweaks, in a single-thread read-only load: why not?

The problem is that InnoDB was designed assuming that the buffer pool being able to cache at least the most used/upper parts of the primary key, which -assuming you can select any row of the table- would create an excessive amount of traffic between the main memory and disks. With the more and more popular flash drives, some parameters of InnoDB have been tuned to allow faster and faster disk access.

In fact, in reality your biggest problem would be inserting. You probably won't be able to have such a tall table due to the increasing performance penalty of checking for uniqueness on the PK for every row inserted. That is why InnoDB works very well when the working set is mainly on the buffer pool, but not when it goes beyond that. Additionally, structures like the change buffer, transaction log etc. would be easy overloaded on insertion. You can see a big slowdown on the blue line once a threshold is surpassed:

Note: the previous image stops inserting into InnoDB when the table it reaches ~60GB.

In a nutshell, InnoDB is not designed for that kind of operations. Solutions? Splitting the table is usually the number one call- specially because partitions tend not to be access with equal frequency. Compression could help with lower storage requirement, but it may change the bottleneck to the CPU on inserting, and it may make things worse regarding caching.

Probably the best option would be to go for a different engine, which works well for lots of rows that is disk-bound. TokuDB is one of the engines that has been heard a lot lately that tries to cover that market gap, and has some interesting benchmarks.


Sql Server 2008 R2 DBCC SHRINKFILE Severe Error I am trying to run the following command in Sql Server 2008 R2:

DBCC SHRINKFILE('filename')

When I try to run this command, I get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Can anyone tell me why I am getting this error and how to resolve this issue?

Thanks!

Have you tried specifying both the file name and the target size in MB, e.g.

DBCC SHRINKFILE('filename', 20);

Or the TRUNCATEONLY option, which doesn't try any of the nasty stuff like reorganizing all of your physical data:

DBCC SHRINKFILE('filename', TRUNCATEONLY);

Also you could try making sure the database is in single user mode first:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

...and turn off any backup / log backup or other maintenance jobs that may be conflicting with your shrink operation. If it still occurs, please run a CHECKDB against this database and let us know the results:

DBCC CHECKDB (db) WITH NO_INFOMSGS, ALL_ERRORMSGS;

But just as a side note, you do know that shrinking is bad and should usually be reserved for extraordinary events and circumstances, right?


Prepopulate database with empty / null values [mysql] What would be the best way to pre-populate a database with 100 empty fields and then begin the Auto_Increment?

What I mean to say, I want to reserve the first 100 userfields (ie UIDs which are auto_increment) 0 - 100 for "special users" and have the AUTO_INCREMENT start at 101 (as an example).

Obviously I can't set Auto_Increment to 101 as then it will start the UID at 101. And, I don't know beforehand how many special users there will be, however 100 sounds like a reasonable number to reserve.

What kind of null data can I insert and how would you go about this?

Actually, yes you can start an empty table's auto_increment from 101.

SAMPLE CODE

use test
drop table if exists fizzydrink;
create table fizzydrink
(id int not null auto_increment,num1 int,num2 int,primary key (id));
show create table fizzydrink\G
alter table fizzydrink auto_increment = 101;
show create table fizzydrink\G
insert into fizzydrink (num1,num2) values (1,2),(3,4),(5,6);
select * from fizzydrink;

SAMPLE CODE EXECUTED

mysql> use test
Database changed
mysql> drop table if exists fizzydrink;
Query OK, 0 rows affected (0.02 sec)

mysql> create table fizzydrink
    -> (id int not null auto_increment,num1 int,num2 int,primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> show create table fizzydrink\G
*************************** 1. row ***************************
       Table: fizzydrink
Create Table: CREATE TABLE `fizzydrink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table fizzydrink auto_increment = 101;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table fizzydrink\G
*************************** 1. row ***************************
       Table: fizzydrink
Create Table: CREATE TABLE `fizzydrink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into fizzydrink (num1,num2) values (1,2),(3,4),(5,6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from fizzydrink;
+-----+------+------+
| id  | num1 | num2 |
+-----+------+------+
| 101 |    1 |    2 |
| 102 |    3 |    4 |
| 103 |    5 |    6 |
+-----+------+------+
3 rows in set (0.00 sec)

mysql>

CREATE 100 INITIAL USERS

use test
drop table if exists fizzydrink;
create table fizzydrink
(id int not null auto_increment,num1 int,num2 int,primary key (id));
show create table fizzydrink\G
insert into fizzydrink (id) values
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
select * from fizzydrink;

You cannot add user id 0 because the auto_increment interprets it as GET NEXT ID

GIVE IT A TRY !!!


Setting MAX memory for SQL servers depending upon the model of CPU I have a general question regrading setting up the MAX SQL memory settings-

For our current CPU with below specs, server team bumped up OS memory from 400GB to 1TB:

Intel® Xeon® Processor E5-4640 v2 - It's a 4 socket 10 core per processors with HT enabled so total of 80 logical processors 4 NUMA nodes

Looking at the specs on intel site here max memory supported seems to be 768.

So what does that exactly mean in terms of SQL space? even OS has 1 TB should one be setting MAX memory settings based on 1 TB or 768 GB? We can see issues from SQL when trying to bump up the MAX memory settings lets say over 850 and when we lower it back to 400 goes back to normal.

Issues when bump up SQL MAX memory - App time outs for queries running over 3 secs based on time out settings.

Tested this couple of times and looks to be same. Need to understand why

Appreciate your inputs on this.

The max memory setting is how much RAM SQL will use. It will usually not use more then the max memory set. (Following Shanky's comment, see below for the exception to that rule): Quoting from  Memory Management Architecture Guide

  Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that
  are loaded into the SQL Server process. Examples of such memory
  allocation requests include allocations from extended stored procedure
  DLLs, objects that are created by using Automation procedures (sp_OA
  calls), and allocations from linked server providers.

Now if your hardware has 1 TB but only 768Gb are recognize in the OS, setting up the max memory to higher then 768Gb will allow SQL to use all available RAM (all the RAM detected by the OS) and this could cause potential issue with the OS as it will have to do memory pressure on SQL to claim it back.

Set the SQL MaxMemory setting depending on how much RAM is seen by the OS and it should be all good. (Leaving at least 4 Gb for the OS)

P.s. Do not set min memory setting the the same value as the max memory. Set it lower as SQL will not release RAM under the min value when there is memory pressure and this could lead to an OS crash.


SQL Server 2012 failover cluster node won't start cluster services We have 2 node SQL server failover cluster setup in our environment.

Node2 SQL server machine got shut down.

I turned it back on and connect back to the failover cluster.

It successfuly connected however, node1 cluster service wont start even if I start it.

Under the Network connections on the failover cluster the SQL ethernet is unavailable. I try to do IPconfig on node1 and I can only see the machine's IP address, in node2 I can see the Windows cluster IP and SQL cluster IP. I ran cluster validation report and this is the one that stood out:

Node SQL-01.ourserver.com is not reachable from node SQL-02.ourserver.com. It is necessary that each cluster node can
communicate each other cluster node by a minimum of one network path (though multiple paths
are recommended to avoid a single point of failure). Please verify that existing networks
are configured properly or add additional networks.

node2 also got that same error saying node2 is not reachable from node1.

What can I do to start cluster service on node1 back on?

I'm not sure if this information would be helpful, but after this windows and SQL cluster got created, we changed the name of the machines

I have had not exactly the same but similar issues described here, here and here.

  What can I do to start cluster service on node1 back on?

when a node feels alone in a windows server cluster setting, it loses the quorum.

Then, to start the windows cluster failover service without the quorum, you need to force it to start.

it is very important, because generally you would have something like this:

windows cluster name - the name that all the applications try to connect to: le't's say it is called the_server
but actually the_server does not exist, what exist is node1 and node2 (plus maybe a quorum disk or a shared storage for quorum purposes) - so even if you have only one node left, you need the windows cluster failover service running, so that all applications can find the_server

The way I would do that is using powershell:

Import-Module FailoverClusters

$node = "Always OnSrv02"
Stop-ClusterNode -Name $node
Start-ClusterNode -Name $node -FixQuorum

(Get-ClusterNode $node).NodeWeight = 1

$nodes = Get-ClusterNode -Cluster $node
$nodes | Format-Table -property NodeName, State, NodeWeight

this is described in detail here:

Force a windows server failover cluster to start without a quorum

To force a cluster to start without a quorum:

Start an elevated Windows PowerShell via Run as Administrator.
Import the FailoverClusters module to enable cluster commandlets.
Use Stop-ClusterNode to make sure that the cluster service is stopped.
Use Start-ClusterNode with -FixQuorum to force the cluster service to start.
Use Get-ClusterNode with -Propery NodeWieght = 1 to set the value the guarantees that the node is a voting member of the quorum.
Output the cluster node properties in a readable format.

First, get hold of the failover cluster manager application, let's have a look at what we have got:

This is how it looks like:

Check the servers inside the nodes:

This is the normal way to manage the clustering services.

However, when there is no quorum we need to force the service to start.
For that you need to follow this link.

just don't forget the way to run the powershell:

The way to run this script is command by command
First this:

Import-Module FailoverClusters

Then

$node = "SQLPROD2"
#Stop-ClusterNode -Name $node
Start-ClusterNode -Name $node -FixQuorum

(Get-ClusterNode $node).NodeWeight = 1

$nodes = Get-ClusterNode -Cluster $node
$nodes | Format-Table -property NodeName, State, NodeWeight

and all good - with only one node up and running - just until we add a new node (but no downtime and no application is upset and not connecting to the_server):


Using postgres with the command line without pgAdmin I started working with PostgreSQL and till now I only had issues with it ( pgAdmin3 issues the most ) now I work a lot with MySQL and sometimes on the command line by just doing

mysql -u ... -h localhost -p for example and I'm connected through the command line.

Is there also a way to connect to the PostgreSQL database without pgAdmin and run it through the command line (Terminal) (I am working on OS X Mountain Lion).

p.s. I am also not a real big fan of letting PostgreSQL create a user. It annoys me when I start up my Mac and see PostgreSQL as user in the login screen. Is it possible to avoid that?

The PostgreSQL command line client is called psql.  You can do pretty much everything with it (apart from taking dumps, for example), you'd better reading its documentation.  Try running locate psql in a terminal and you will find its binary.

As far as I know, on OS X (and typically on other systems, too) pgAdmin is bundled with the server package, so you won't be able to install PostgreSQL without it.  Here I am not very well informed, so handle this with care.

Regarding the postgres user, on linux machines it is a must to have it (and again, it looks the same on all other systems, too), you simply cannot run the server with other users.  If you don't use PostgreSQL, the user will sit there quietly, won't eat your food and drink your beer, so you need not to worry about it.


MSSQL efficient paging including a join on big tables We have a table with products and a table with a current selection/assortment of products. Now need to join them create an export file by some .net code.

Currently using query like this:

SELECT * FROM ASSORTMENT a
INNER JOIN PRODUCTS p on a.clientID = 38 and a.productID = p.productID
ORDER BY a.ID_ROW OFFSET 100000 ROWS FETCH NEXT 1000 ROWS ONLY

Problem is that the assortment can contain discontinued products, that are not existing in products table anymore, so need a join to verify. Also need each time exactly same amount of rows per select, e.g. 1000.

The query gets slower if the offset gets higher because it does a full join of products and assortment table each time. On 3 Million products and an assortment of 200.000 articles it takes multiple seconds to fetch next 1000 if the offset is over 50.0000

Is there a more efficient way of doing this?

You didn't describe the problem very precisely so I made some guesses about table structures and data volume. I put 3 million rows in both tables:

CREATE TABLE dbo.ASSORTMENT (
    ID_ROW BIGINT NOT NULL,
    clientID INT NOT NULL,
    productID BIGINT NOT NULL,
    FILLER VARCHAR(2000) NOT NULL,
    PRIMARY KEY (ID_ROW)
);

INSERT INTO dbo.ASSORTMENT WITH (TABLOCK)
SELECT t.RN, 30 + t.RN % 15, t.RN, REPLICATE('Z', 2000)
FROM
(
    SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t (RN)
OPTION (MAXDOP 1);

CREATE TABLE dbo.PRODUCTS (
    productID BIGINT NOT NULL,
    FILLER VARCHAR(2000) NOT NULL,
    PRIMARY KEY (productID)
);

INSERT INTO dbo.PRODUCTS WITH (TABLOCK)
SELECT t.RN, REPLICATE('Z', 2000)
FROM
(
    SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t (RN)
OPTION (MAXDOP 1);

Getting the first thousand rows is fast. It only requires 15 milliseconds of cpu time on my machine:

SELECT *
FROM dbo.ASSORTMENT a
INNER JOIN dbo.PRODUCTS p on a.clientID = 38 and a.productID = p.productID
ORDER BY a.ID_ROW OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY
OPTION (MAXDOP 1);

If I change the OFFSET to 50000 rows the query plan looks the same but the query now takes 1700 ms of CPU time on my machine:

Why is the query so much slower? ORDER BY with an OFFSET requires SQL Server to scan past the unwanted data. For my data set SQL Server SQL server reads 764993 rows from ASSORTMENT before finding the 51k rows that it needs. It also does 51k index seeks to PRODUCTS before returning the 1000 rows that you requested.

Let's take a step back. Do you really need to select all columns from both tables? Your question said that the join to PRODUCTS is only there for data integrity, so why do you need all columns from that table? Unnecessary SELECT * is a bad habit which is best avoided. It would make a great New Year's resolution!

One standard solution would be to only select the columns that you need and to make covering indexes on the two tables. I'm going to assume that you absolutely need all columns from both tables. In that case you want the query optimizer to do the scan over as little data as possible, but in terms of number of rows and number of columns. To make that possible I'll create the following indexes:

CREATE INDEX IX_ASSORTMENT ON dbo.ASSORTMENT (clientID, ID_ROW) INCLUDE (productID);

CREATE INDEX IX_PRODUCTS ON dbo.PRODUCTS (productID);

A query to get just the clustered indexes of both tables now just takes 63 ms of CPU time on my machine:

SELECT a.ID_ROW, p.productID
FROM dbo.ASSORTMENT a
INNER JOIN dbo.PRODUCTS p on a.clientID = 38 and a.productID = p.productID
ORDER BY a.ID_ROW OFFSET 50000 ROWS FETCH NEXT 1000 ROWS ONLY
OPTION (MAXDOP 1);

The big differences are that SQL Server doesn't have to read rows for which ProductId is not 38 and it doesn't need to read data pages that contain unnecessary columns. Adding all of the columns back into the query bumps up the CPU time requirement to 80 ms but that's still better than a 20X improvement than before:

SELECT a.*, p.*
FROM
(
    SELECT a.ID_ROW, p.productID
    FROM dbo.ASSORTMENT a
    INNER JOIN dbo.PRODUCTS p on a.clientID = 38 and a.productID = p.productID
    ORDER BY a.ID_ROW OFFSET 50000 ROWS FETCH NEXT 1000 ROWS ONLY
) t
LEFT OUTER JOIN dbo.ASSORTMENT a ON t.ID_ROW = a.ID_ROW
LEFT OUTER JOIN dbo.PRODUCTS p ON t.productID = p.productID
OPTION (MAXDOP 1);

If you need the query to be faster than that you can consider using an anchored solution as described here.


Return value formatted similar to if casted as money My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places.

E.g.

From

1826.5695600000
1826.3000000000
34.2300000000
0.0030000000

To

1826.56956
1826.30
34.23
0.003

If I cast these values as money I lose everything after 4th decimal place, but I need to achieve something similar but with min 2 max 10 decimal places. It does not matter if it is a string at the end, all that matters that I can extract value in this format.

My first try was to cast to float, find decimal point and accordingly cast the value as decimal(20,variable) but that was impossible, so I am now stuck in my mind how to achieve this.

These values are going trough SSIS processes

Extracted from Database
Staged
Pushed to client's import file

This is really ugly and hopefully serves as a lesson as to why you should be fixing the receiving system to not be so fussy about trailing zeroes.

DECLARE @t TABLE(a DECIMAL(20,10));

INSERT @t VALUES
(1826.5695600000),
(1826.3000000000),
(  34.2300000000),
(   0.0030000000);

DECLARE @d INT = 10; -- number of decimal places in source data!

;WITH s(a,dot,nz) AS
(
  SELECT
    CONVERT(VARCHAR(32),a),
    PATINDEX('%.%',CONVERT(VARCHAR(32),a)),
    PATINDEX('%[^0]%',REVERSE(a))
  FROM @t
)
SELECT LEFT(a,dot)+SUBSTRING(a,dot+1,@d+1-nz)
  + COALESCE(REPLICATE('0',nz-(@d-1)),'')
FROM s;

Results:

1826.56956
1826.30
34.23
0.003

This assumes the source data type is a decimal(something,10). If it is a string, you need to have some extra precautions in here for values that aren't numbers (or aren't really numbers in this sense).


sp_send_dbmail stored procedure send with attachment I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the customer as an attachment.

I'm looking for a more permanent solution, so I intend on using sp_send_dbmail stored procedure to run the query and send it as an attachment.

Everything works but the formatting of the message. Initially I tried to attach the output as a CSV file with a @query_result_seperator = ',' but the results were everywhere!

When I run the report normally, the output looks fine in SQL. But sending it as a CSV or just in the message body doesn't.

I think it might work better if I export the output as HTML and send that as an attachment/or as XML but I don't know how to do this.

Does anyone have any suggestions?

Thanks in advance!

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server.

Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is:

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

You would then attach the file to the email in sp_send_dbmail.

USE msdb;
GO

EXEC sp_send_dbmail
  @recipients='mark@mark.com',
  @subject='Client Report',
  @body='Please find your latest report attached',
  @file_attachments='D:\MyTable.csv';

You could, if you want, attach multiple files to one email.


Why are periodic restarts required to keep my instance performing well? We've got a production DB server on SQL 2005.  Everything runs normally for a while, but after a couple weeks we see a notable performance drop.  Only restarting SQL Server brings performance back to normal.

Some background:

Running over 1200 databases (mostly single tenant, some multi-tenant). Before anyone lectures on moving to only multi-tenant, there are valid reasons for keeping this structure......
RAM is 16 GB.  After restarting, it doesn't take too long for SQL Server to go back to 15 GB usage.
Active DB connections is about 80 connections - which we feel is fairly healthy considering there is one connection pool per web server per process - so we don't have a connection leak issue.

We've tried several things in non-peak times:
- Run DBCC DROPCLEANBUFFERS (with a CHECKPOINT) to clear data cache. It has no effect, nor does it clear any of the RAM usage).
- Run FREEPROCCACHE and FREESYSTEMCACHE to clear query plans and stored proc cache. No effect.

Obviously restarting SQL Server is not ideal in an active production environment.  We're missing something.  Anyone else go through this?

UPDATE: April-28-2012
Still battling this problem.  I've lowered the memory for SQL Server to 10 GB, just to rule out any contention with the OS.  I'm getting closer to narrowing it down, but need some help from my next step.

Here's what I found, after restarting SQL Server, the page file hovers between 12.3 GB and 12.5 GB.  It'll stay that way for days.  Total server threads will hang out between 850 and 930 - also stable and consistent for days on end (sqlserver is steadily between 55 and 85 of those depending on traffic).

Then, there's "an event".  I have no idea what the event is, I can't see it in the logs, and I can't see anything consistent on the day of week or time it happens, but all of the suddent he pagefile jumps to either 14.1 or 14.2 GB, and the threads jump to between 1750 and 1785.

Checking perfom when this happens, over 900 of those threads are sqlserver.  So I go to sp_who2 to see where are these threads are coming from...and there's just the used 80 or so db connections.

So....does anyone have any ideas how I can locate where the rest of these 900 threads on SQL server are, and what they are doing?

UPDATE: June-01-2012
Still battling the issue. For anyone reading this still, the issue with the threads jumping up has been resolved. This was caused by autodated ComVault backup software. It was creating a thread trying to backup databases that were no longer there (it was maintaining a list of previous databases) rather than just backing up the current databases.

But - the issue still remains, and we have to restart every week, give or take a few days.  Working with the Rackspace team to see if they can shed any light.

You say that everything is fine, then after a couple of weeks, performance drops.  (Usually, people claim that performance drops quickly, or at specific times, or at seemingly random intervals. That could mean bad I/O performance or lock storms or cpu-intensive queries running at wierd times, or a heavyweight scheduled job or lack of indexing or bad stats causing cpu-intensive queries or disk reads. Or other stuff.) Weeks is unusual.

My hypothesis is that another application on your server is leaking memory. I have seen this with virus software (every DBA's favorite server software villain) and 3rd party monitoring software. I would double check the memory usage of SQL Server, over time, and I'd grab all of the memory usage of all of the other applications on the box as well. If you have hard limits set on SQL Server's memory usage and have it set to not allow paging, it might be other apps that are getting paged out and eating up I/O capacity.

It's not hard to look for. If you are not already keeping metrics on the server, I would just start up Perfmon and have it grab a sample every 30 or 60 minutes. After a few days, you may see another applications memory usage creep upwards.

Are there error messages in the SQL Server log stating that "significant portions of sql server have been paged out"? That would also be a big clue.


Oracle - REPCAT$ Tables I accidently dropped the REPCAT$_USER_PARM_VALUES table from the system schema in my oracle db. Will it cause any problems in the future? What are these repcat$ tables for?

These tables are related to Oracle Advanced Replication.

Although it is not advisable to drop them, you might just be fine if you are not using any Advanced Replication features.

Related :  DBMS_REPCAT package


Optimizing simple query that performs a large table migration There are two tables trains and train_statuses.
train_statuses already has a column train_status which is an ENUM.
CREATE TYPE train_status AS ENUM('queued', 'running', 'succeeded', 'failed', 'cancelled');

As part of the migration, I will be adding a new column status in trains table and updating it with only the final status from train_statuses. The final statuses are: 'succeeded', 'failed', 'cancelled'.
Other relevant details about the tables:
Indexes:
Trains table:
    "trains_pkey" PRIMARY KEY, btree (id)
     "idx_trains_queued_at" btree (queued_at DESC)

Train statuses table:
    "train_statuses_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)
    "idx_train_statuses_id_updated_at" btree (id, updated_at)
Foreign-key constraints:
    "train_statuses_id_fkey" FOREIGN KEY (id) REFERENCES trains(id) ON DELETE CASCADE

This is the query that I am planning on using to update status in trains table.
UPDATE trains AS t SET status = (
    SELECT status FROM train_statuses ts
    WHERE ts.id = t.id AND status in (?)
    ORDER BY ts.updated_at DESC LIMIT 1
) WHERE ID in (
    SELECT id FROM trains LIMIT 100
)

I would be calling this query in a for loop until I get the number of rows affected as zero.
Even though this is a simple migration, the table involved is extremely huge and occupies ~150GB on production. Please review this query and suggest any possible optimizations that could be done taking into consideration the size of the table.
Thanks

An UPDATE with a co-related subquery is typically quite slow, because the sub-query is run once for each row that is updated. It's typically faster to do that only once. To get the latest status for each train, you can use DISTINCT ON and join to the result of that:
update trains t
  set status = ts.status
from (
  SELECT distinct on (ts.id) ts.id, ts.status
  FROM train_statuses ts
  WHERE status in (?)
  ORDER BY ts.id, updated_at DESC
)
WHERE t.id = ts.id;


What is meant by a blind write in a schedule? I know that every conflict-serialisable schedule is view-serialisable but the converse is not true. I read in a webpage that when a schedule is view-serialisable but not conflict-serialisable then there exist some blind writes. So what is a blind write?

If there is no read that happens prior to the first write then it is said to be a blind write.

Example. Consider the following schedule:

W3(X) is a blind write, as there is no read before write [R3(X) before W3(X)]
W2(X) is not a blind write, as a read happens before write [R2(X) before W2(X)]


Mongodb exception: connect failed Here's the environment:

Linux(x64): 16.04
MongoDB: 3.2.15

Error:

root@system-test:~# mongo
MongoDB shell version: 3.2.15
connecting to: test
2017-07-25T07:31:28.528+0000 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, in(checking socket for error after poll), reason: errno:111 Connection refused
2017-07-25T07:31:28.530+0000 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:229:14
@(connect):1:6

exception: connect failed
root@system-test:~#

When I tried to see the status I get this:

root@system-test:~# sudo service mongodb status
● mongodb.service - High-performance, schema-free document-oriented database
   Loaded: loaded (/etc/systemd/system/mongodb.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2017-07-25 07:45:32 UTC; 1min 8s ago
  Process: 1721 ExecStart=/usr/bin/mongod --quiet --config /etc/mongod.conf (code=exited, status=14)
 Main PID: 1721 (code=exited, status=14)

Jul 25 07:45:32 system-test systemd[1]: Started High-performance, schema-free document-oriented database.
Jul 25 07:45:32 system-test systemd[1]: mongodb.service: Main process exited, code=exited, status=14/n/a
Jul 25 07:45:32 system-test systemd[1]: mongodb.service: Unit entered failed state.
Jul 25 07:45:32 system-test systemd[1]: mongodb.service: Failed with result 'exit-code'.

How it happened:

I tried to stop MongoDB and did the following

sudo service mongo stop [This didn't work].

Then i tried kill - 2 <PID> [Seem didn't worked too]

But after this command #mongo stopped working and getting the above error.

Please help guys

sudo service mongo stop [This didn't work]

As MongoDB BOL  mongo is an interactive JavaScript shell interface to MongoDB, which provides a powerful interface for systems administrators as well as a way for developers to test queries and operations directly with the database. mongo also provides a fully functional JavaScript environment for use with a MongoDB.

And mongod is the primary daemon process for the MongoDB system. It handles data requests, manages data access, and performs background management operations.

As simple language we can say that mongo is shell interface for MongoDB. And mongod is server , which handles data requests, manages data access, and performs background management operations.

So, in place sudo service mongo stop it should be the below mention command

  sudo service mongod stop

As MongoDB documented Here Some of basic procedure to check MongoDB services in RHEL is as mention below:

Start MongoDB

sudo service mongod start

Stop MongoDB

sudo service mongod stop

Restart MongoDB

sudo service mongod restart

Verify that MongoDB has started successfully

sudo chkconfig mongod on

Begin using MongoDB

Start a mongo shell on the same host machine as the mongod

mongo --host 127.0.0.1:27017


"All joins can be rewritten to sub queries", is this statement true? I wonder if there any case that a join can not be converted to a sub-query.

BTW: I am not discussing advantages and disadvantage of using sub-query or using join.

As far as I know, a JOIN [INNER JOIN], from the theoretical point of view, is a projection of a CROSS JOIN (every combination of two tables). If you can get a cross join using a subquery and apply any function on it, then you have a perfect substitute. I think you can always transform:

SELECT T1.A, T2.B
FROM T1
JOIN T2
ON condition(T1.C, T2.D)

into:

SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2))

A similar conclusion can be reached with LEFT [OUTER] JOINS and RIGHT [OUTER] JOINS (the other types supported on MySQL), by including the NULL value:

SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2) UNION SELECT NULL)


Merging the sysdb database (a dependency due to conversion from Oracle) into my SQL Server 2014 Express Database I am looking to convert my SQL Server 2014 Express Database to Azure SQL Database.

My SQL Server database was automatically converted from Oracle a number of years ago, and needs the sysdb database (provided by Microsoft) as a dependency to provide user defined functions that emulate Oracle functions.

I ran the Data Migration Assistant to check for compatibility, and the main problem seems to be that "Cross-database references not supported in Azure SQL Database". This relates to the dependency on the sysdb database.

The two recommendations of the Data Migration Assistant are either:

Move the dependent datasets from other databases into the database that is being migrated. OR
Migrate the dependent database(s) to Azure and use 'Elastic Database Query' functionality to query across Azure SQL databases.

I would eventually like to get rid of all Oracle related references, although I do not have the resources to do that right now. I feel that the best thing to do is to merge the sysdb database with my database (so that it would then work with Azure SQL Database) and then as a long-term project slowly rid myself of Oracle dependencies.

Any ideas about how to achieve this? (Or a better idea?)

In SQL Server, you can take advantage of the SCHEMA and SYNONYM, the former which are similar to Oracle's User (hence the sysdb database) and the latter lets you hide the actual object name from your application. See the documentation for SCHEMA and SYNONYM.

1) On your target DB, create the sysdb schema:

USE [TargetDB]
GO
CREATE SCHEMA [sysdb] AUTHORIZATION [dbo]

2) In your nonproduction environment, merge all the relevant procedures, tables, and any required object into this schema.

You can either use the SQL Server Import and Export Data utility or script it out yourself.

3) If you are unable to specify a schema in your application, create SYNONYMS for each object you require:

USE [TargetDB]
GO
CREATE SYNONYM dbo.usp_myproc FOR [TargetDB].sysdb.usp_myproc
GO
GRANT EXECUTE ON dbo.usp_myproc TO [USER]
GO

Qualification: you can only specify the current Database or tempdb (if the object is a temp table) and There might be some issues if there are duplicate objects in each schema, but otherwise this will work just fine. :)


Ola scripts = log shipping and transaction logs We use the Ola Hallengren scripts for full/diff/tran log backups.  Ola's site indicates that log shipping is compatible unless we are using transaction logs.  Why is that?

I believe your answer lies in another answer here on StackExchange.  Transaction log shipping together with backup job = conflict?.

The log shipping process does its work by taking log backups. There
cannot be another job that does the same, it will break the log backup
chain. See Using Log Shipping as Part of a Recovery Plan. Your
recovery strategy should clearly document the location of the log
backups as taken by the log shipping job and detail step-by-step
instructions how to recover the database using these log backups. You
should also test this strategy to validate it.
If you create a maintenance plan (or Ola backup) for a database that is configured as
a log shipping database, do not have the maintenance plan create
transaction log backups. If a maintenance plan and log shipping both
attempt to create transaction log backups of the same database, log
shipping will not function correctly. You can do full and differential
database backups through your maintenance plan (or Ola backup) without any conflict
with log shipping.

According to Ola's Frequently Asked Questions
Does the SQL Server Maintenance Solution support log shipping?

The SQL Server Maintenance Solution supports log shipping. Full and
differential backup, integrity check, and index and statistics
maintenance are performed on the primary database. Transaction log
backups are not performed on databases that are involved in log
shipping.

So, if you implement Log Shipping, just keep in mind that THAT process will be taking your transaction log backups and NOT Ola's process.


Service Principal as SQL Active Directory Admin, does it use Graph API? I'm looking to use a service principal as the server admin, so it can be used in a release pipeline to create further active directory users.

I'm successfully able to make the service principal the server admin* and connect to the database using an Access token, so the service principal authentication works fine, which is great nice and an interesting challenge.

*EDIT: The AAD server admin was creating via the ARM template, notably if you tried creating manually via the portal, you cannot select service principals as admin users. But interestingly you can see the service principal name ok in the portal after creation using ARM instead. And you can login successfully with AAD auth, so it appears a server principal admin works fine.

However when creating further users, they never seem to be found with the error:

"Principal 'name here' could not be found at this time. Please try again later."

However when logged in as myself I am able to create other users fine.

Which leads me to think is SQL Server using some sort of Active Directory impersonation to validate the subsequent users / service principals exist?

If I were to grant my service principal access to the Graph API would that give it enough permissions? If so what is the minimum rights necessary?

I also see that Azure SQL Server has created a managed service identity in the background, so I wonder if that's also at play?

Also I saw on managed instances that they explicitly require a service principal with AAD rights to hook into AAD - which I presume is like an Azure SQL Server's MSI? But Azure SQL Server doesn't mention this with an AAD admin being enough.

I'd love to know how the underlying mechanism for the active directory hookup works as it could help me investigate my challenges further.

Got an official answer from Microsoft today:

  I have confirmed that creating AAD users and groups is currently NOT supported when using Service Principal accounts. This particular limitation is only specific to database contained users.


Oracle UPDATE statements lock race condition Say I have a table MY_TABLE without optimistic lock column. I have the following update statement that will be executed with org.hibernate.SQLQuery:

UPDATE MY_TABLE SET NAME = ? WHERE SOME_NUMBER = ? AND NAME = ?

In some place in my Java code, I'll create a new async thread to execute this update. Say that at a given time after some seconds this thread is executed:

Thread 1:

UPDATE MY_TABLE SET NAME = 'TEST2' WHERE SOME_NUMBER = 3 AND NAME = 'TEST'

This operation can take some minutes. Few milliseconds later the following thread is executed:

Thread 2:

UPDATE MY_TABLE SET NAME = 'TEST3' WHERE SOME_NUMBER = 3 AND NAME = 'TEST'

I need to make sure that, since the rows affected are the same, the UPDATE from the first thread is executed before the one from the second thread.

Does the default lock strategies from Oracle already guarantee that? Or do I need anything else?

Thanks!

As you can see from the documentation you supplied, oracle places an exclusive lock on the row during an update. This means that the second transaction in thread 2 will wait until thread 1 has committed and thereby releasing the lock on the row. If you only execute the UPDATE statement oracle will handle it for you.


Updating 700 million rows to same value I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.

I don't have admin access, or access to an admin, so this needs to be accomplished with basic sql and no temp table creates.

Further complicating matters is if I try to just do a simple update where 1=1, it runs out of redo space.

The way I have it running right now is looping such as this:

loop
  update mytable set mycolumn = '1' where mycolumn is null and rownum < 50000;
  commit;
end loop

but I know this is probably naive and there must be a quicker and more elegant solution.

If you have the space, you can CTAS using minimal undo/redo. If you have any indexes at all, doing it any other way will be very slow and generate logging like crazy.

In the case where you have a single IOT without any secondary indexes, or a single table cluster, you could step through the primary/cluster key updating in chunks without having to rescan the whole table to find the fields that have not yet been updated.

--edit

  I am not able to create a secondary table ... There are a couple of
  indexes, but none of them involve the column I am updating.

Then I suggest break the table up into chunks for processing using something you are indexing on (even if it is a single column, you can split it into ranges of values) This will do a FTS once instead of once for each chunk as in your code. You will have to live with an awful lot of redo and will wipe out your undo space too (so no flashback subsequently)

--edit2

if you can add/rename/drop columns, you can do this very efficiently, but only on 11g