if block fails to create temp table in a procedure I'm attempting to do this in a procedure:

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
END

I get the error:

Msg 2714, Level 16, State 1, Line 10
There is already an object named '#bTemp' in the database.

Why is this happening and is there a work around?

Update

I've attempted to add a DROP statement as suggested here, but it still does not work:

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
END

Per the documentation:

  If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

I ended up creating the table before the IF block like so:

DECLARE @a bit = 1;
BEGIN
    IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
    BEGIN
        DROP TABLE #bTemp;
    END

    CREATE TABLE #bTemp (
        [c] int);

    IF @a = 0
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END
    ELSE
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END

    DROP TABLE #bTemp;
END


Bicycle Sharing Scheme - ER Diagram Please compare the directions below to my ER Diagram and tell me if anything is wrong with my ER Diagram Please I think its accurate enough.

Oxford City Council is preparing to launch an innovative program that will provide community members with short-term access to bicycles through self-service terminals placed at convenient locations across the city. Each terminal is given a unique identification number, street address, and maximum storage capacity. The city keeps track of the bicycles currently available at each terminal for logistical reasons.
The program will offer subscriptions that include weekly, monthly, and annual memberships. Membership costs range from a few dollars each week to over $100 for an annual membership. Information stored about each member includes full name, email, contact phone numbers, residential address, driver's license number, and membership status. A subscriber may only have one active membership at any given point in time. In addition to memberships, visitors or casual users can access the system by purchasing a $5 one-day pass using a credit card.
The city has made an initial purchase of 700 bicycles, each of which is fitted with a GPS tracking device. The city records the unique ID of each bicycle, along with it's make, model, color, and size. To reduce costs, private-sector companies are invited to sponsor individual bicycles and self-service terminals and, in return for a fee, their logo will be displayed prominently on the sponsored asset. The city tracks the name of each sponsoring company along with their address, contact name, email, telephone number, and a list of the assets that they have chosen to sponsor.
Each rental transaction is fully automated and records details of the user, bicycle, pick-up time and location, drop-off time and location, and total rental length. A user may not borrow more than one bicycle at any given point in time.

I think there would need to be a MemberPhone table, since the description mentions storing multiple phones per member
I would create tables to store the possible values for for BicycleMake, Bicycle (which contains an attribute linking to a model), BicycleColor, and BicycleSize. Given these tables, there wouldn't need to be a reference to the BicycleMake in Bicycle.
I feel like the Total Rental Length attribute doesn't need to be stored, since this could be derived from the pick-up time and drop-off time. Or better yet, the drop-off time doesn't need to be stored, since it can be derived from the pickup time and total length (DateTime + int is smaller than DateTime + DateTime).
How were you planning on storing the sponsorships?
I suspect that subscription information would need to be stored, such as when a subscription was purchased, and what type it is (monthly, yearly, daily, ...). If this does need to be stored, then it's possible that the membership status can be derived from the subscription information, if the membership status is simply whether the member has an active subscription or not. I would ask questions about this...
Bicycles should have an attribute for what terminal it's currently stored in (perhaps a null-value meaning it's not currently in a Terminal). Otherwise, how do you know which terminal a bicycle is in if it has yet to be rented?
I think the Terminal_Info table is unnecessary, since the number of bicycles at a terminal is duplicating data.
I feel like the GPS tracking number would need to be stored for the bicycles. I would ask questions for this...


Is it more efficient to create check constraints on PostgreSQL table before or after bulk load? I am creating a table in PostgreSQL and bulk load with data first. PostgreSQL's documentation recommends to insert large volumes of data first and then create indexes and foreign key constraints. What's about check constraints? Is it better to create them before inserting initial data or after? Or does it matter?
For example, a table will have an index on time, NOT NULL constraint on it, check constraint on time interval and possibly on device. If no bulk load, the definition will be:
CREATE TABLE conditions(
    time timestamptz NOT NULL,
    device int,
    value float,
    CONSTRAINT cond_time CHECK ("time" >= '2020-01-16 01:00:00+01'::timestamp with time zone
                            AND "time" < '2020-01-23 01:00:00+01'::timestamp with time zone),
    CONSTRAINT cond_device CHECK (my_hash_function(device) >= 1073741823
                              AND my_hash_function(device) < 1075441823)
);
CREATE INDEX conditions_time_index ON conditions(time);

The index will be create after the bulk. The data to bulk load is cleaned and satisfies the constraints. The constraints are needed in future during normal operations.
Will it be more efficient if the check constraints and NULL NOT are created before bulk load or after? Does it matter if an index will exist on the column targeted in the check constraint?

You can benchmark it, but it shouldn't make a difference. The check expression will be evaluated for each row, and it only uses the data from that row. It should make no difference if that takes place while you load the data or afterwards.


Recursive query in mysql I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

Here is my working theory: Because you have the table data in InnoDB it may subjected to MVCC and Transaction Isolation in the midst of the InnoDB Infrastructure.

Please notice in my original post that I used MyISAM tables, which are not subject to ACID compliance.

Make a copy of the table, convert it to MyISAM, and run GetAncestry against it. You should find better performance.

Give it a Try !!!


backup/replication over inconsistent network I need to setup a MySQL database server for a small number of local (write) users (~6) in a remote area with somewhat irregular internet access via WiMAX. Outages are not at predictable times or of predictable duration. I will have another small number of users (only really need read access) at a central office and...

I would like to know what backup/replication options are amenable to this scenario?

My fallback seems to be taking a nightly dump and running rsync as a cron job to sync it to local NAS + remote storage (central office + S3).

I'd prefer doing either replication or log-shipping to a remote server at the central office for r/o query users + dumps w/rsync to local NAS + remote S3 backup. But I'm unsure of the effects of extended network outages (hours to day) and/or significant packet loss.

Generally this is not a dba question as much as it turns into a networking issue. You need to decide what you want / what is more important to you: Having backups or having them centrally.

I generally would go for the first - make a backup locally, then move it offline for serious problems coming. This is particularly important given that you have no reliable internet - so unless you do this, you may end up not having backup done at all because internet is bad.

Now, for packet loss - there is not a lot you can do and it kills data transfers. It also generally is a sign of a very bad internet connection. Wimax generally is not exactly an excuse here as much as it is a symptom - a not really widely used technology with better alternatives existing. You can use a relatively cheap point to point wlan link over many km with much higher speed and - very little packet loss. I am writing this on the receiving end of a 4km WLAN based internet connection and my packet loss on most days (when it is not storming or we have lightning) is neglegible. TO fix the packet loss you must address that from a business point - talking with the ISP and make him maintain his equipment properly. High packet loss WILL kill large transfers with not a lot you can do. It also is the main issue here and - totally not a dba related question, as it affects any kind of data transfer.

And again: Given extended network outages that seem to be normal, I would go for a local backup that then gets copied out. Because this means if your internet is down for 3 days, your backups still happen.


MySQL Backup Strategy We are running the backup from the replication server and it is working good, since we are not facing any other issues, some information for better understanding our strategy:

Backup Type: Full Backup
Server : MySQL 5.5
Backup Tool: mysqldump
Interval: Every 24 hours.
Data size at present : 2 TB.

Actually we are in need to store the back up of 13 months but we have only 2 months. The data is growing around 6% in every month. We are running out of Storage.

Recently, we thought of moving into mysql incremental backup. The decided plan is we need to run full backup on every week interval and incremental backup at every 24 hours.

Our main concern is storage, since our data is growing every month, I need to know the answer for following questions.

What is the best possible solution for backup and storing the data considering our situation?
Whether the incremental backup strategy will solve our storage issue?
Is there any other better way to handle this situation?

Any suggestions welcome, thank you in advance.

2 TB sounds too much for mysqldump. Did you try to restore a database from the dump? How much time does it take?

For large databases I would suggest XtraBackup. Not only it's faster, especially when it comes to restoring but it also supports incremental backups.

Weekly full copies sounds reasonable. For daily backups you have two options:

Incremental copy containing changes since last day.
Incremental copy containing changes since last full copy (sometimes this type is called differential backups). I prefer this one because of smaller restore time and higher chance to successfully restore the database.

If you need to store 13 month of data then you'd probably need to store 13 full copies. In that case storage requirements would be similar.

What you can do is to take full copy at month zero and then take a differential copy every moth. Then assuming 2TB initial size and 6% monthly growth the total storage size would be ~4TB.

Month   Database size, TB   Delta, TB
1   1.00    0.00
2   1.06    0.06
3   1.12    0.06
4   1.19    0.07
5   1.26    0.07
6   1.34    0.08
7   1.42    0.08
8   1.50    0.09
9   1.59    0.09
10  1.69    0.10
11  1.79    0.10
12  1.90    0.11
13  2.01    0.11
    Total delta, TB 1.01


Prevent users from using power pivot excel connections to a database A couple of weeks ago my boss connected to our production database via excel and was able to query the database.  Because of how we're doing security to the database (AD groups) he should have access to the database.  I'd rather not have users just willy nilly creating pivot tables against our OLTP databases.  Is there a way to prevent these sort of connections?

The way our security works, when a user accesses the application, it checks the AD group, and if the user is in the group the application lets them in.  We use the same AD group to allow access to the database, the application passes the user's information to SQL Server.

You can do that using LOGON TRIGGER.

something like below :

--Prevent acces from Excel
CREATE TRIGGER tr_stop_excel_users
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END

Refer to my answer here as it has the script to achieve it as well. You have the flexibility of customizing the script to check for Application, Users, etc and depending on what you want to allow or not, the table that governs the script can be modified.


support of multiple databases on different hosts by pgpool2 Let's assume We have 2 databases:
Database1 is located on host1 and host2
Database2 is located on host3 and host4
If I'll use pgpool2 to connect to these 2 databases with single pgpool config for backend configuration:
 backend_hostname0 = 'host1'
 backend_port0 = 5432
 backend_weight0 = 1
 backend_hostname1 = 'host2'
 backend_port1 = 5433
 backend_weight1 = 1
 backend_hostname2 = 'host3'
 backend_port2 = 5434
 backend_weight2 = 1
 backend_hostname3 = 'host4'
 backend_port3 = 5435
 backend_weight3 = 1

With this kind of configuration, will pgpool2 throw an error in case of trying to connect to Database2 for example which is absent on host1 and host2 or it will determine easily necessary hosts which contain appropriate database with error and coordinate client in appropriate way to connect to it?
Why I am asking this, because I don't see that pgpool supports something like conf.d directory where I can put separately hosts with different databases in order to connect to them via clients. Instead I see support only of single config file which is pgpool.conf for backend definition.

no. With this kind of configuration, pgpool2 will not throw an error.
Pgpoll configuration is much more complicated than that. I used to think it as easy as you list config. Later, I just follow the full procedure.
Here for your reference.
https://www.pgpool.net/docs/latest/en/html/example-cluster.html
Good luck


SQL: How to ask for multiple counts from the same table within a join Trying to find 3 separate counts within one column of a table, each with separate WHERE clauses. I've got 2 tables in the query, so one join. Thought this looked correct but it will not run:

SELECT stores.caption,

COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
            AND tasks.deleted_at IS NULL)
            AS TaskTotal ,

 COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
            AND tasks.deleted_at IS NULL
            AND tasks.completed_at IS NULL) AS TaskOutstanding,

COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
            AND tasks.deleted_at IS NULL
            AND tasks.completed_at IS NULL
            AND tasks.deadline < '2019-05-16') AS TaskOverdue

    FROM tasks
    FULL JOIN stores ON tasks.store_id=stores.id
    GROUP BY stores.caption;

MySQL does not have FULL join implemented. You are likely to need only a LEFT join (from stores to tasks):

FROM stores
     LEFT JOIN tasks
     ON tasks.store_id = stores.id

You can't have WHERE or WHEN in a SELECT expression like you try. There is a FILTER feature in standard SQL but that has not been implemented either in MySQL. There are however CASE .. WHEN expressions you can use just fine. Example:

COUNT (
    CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
              AND tasks.deleted_at IS NULL
        THEN 1 ELSE NULL END )
    AS TaskTotal ,

So the query can be written:

SELECT
    stores.caption,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
                  AND tasks.deleted_at IS NULL
            THEN 1 ELSE NULL END )
        AS TaskTotal ,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
                  AND tasks.deleted_at IS NULL
                  AND tasks.completed_at IS NULL
            THEN 1 ELSE NULL END )
        AS TaskOutstanding ,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
                  AND tasks.deleted_at IS NULL
                  AND tasks.completed_at IS NULL
                  AND tasks.deadline < '2019-05-16'
            THEN 1 ELSE NULL END )
        AS TaskOverdue
FROM
    stores
    LEFT JOIN tasks
    ON tasks.store_id = stores.id
GROUP BY
    stores.caption ;

Now, since all 3 expression have a common condition you can simplify by moving that condition to the ON clause - assuming that you don't want any more columns without it.

We can also add table aliases and removed the redundant ELSE NULL from the case expressions:

SELECT
    s.caption,
    COUNT ( t.store_id )
        AS TaskTotal ,
    COUNT (
        CASE WHEN t.completed_at IS NULL
            THEN t.store_id END )
        AS TaskOutstanding ,
    COUNT (
        CASE WHEN t.completed_at IS NULL
                  AND t.deadline < '2019-05-16'
            THEN t.store_id END )
        AS TaskOverdue
FROM
    stores AS s
    LEFT JOIN tasks AS t
    ON t.store_id = s.id
    AND t.created_at BETWEEN '2019-04-01' AND '2019-04-30'
    AND t.deleted_at IS NULL
GROUP BY
    s.caption ;


COM error on UnionAggregate Has anyone ever come across a COMException while working with geography data in SQL Server?

I didn't notice the error until I ran .Reduce() on the original zip code data.  It may have happened before then had I let queries run long enough, but most queries I tried ran for a very long time (up to 5 hours, in once case) before I terminated them.

I'm not entirely sure how to debug something like this, since it's not giving me any useful error code, and I haven't been able to find anything about this sort of error in the documentation.

The error is as follows:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GeographyUnionAggregate":
System.Runtime.InteropServices.COMException: Error HRESULT E_FAIL has been returned from a call to a COM component.
System.Runtime.InteropServices.COMException:
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticCombine(CombineMode combineMode, GeoData g1, GeoData g2, Double eccentricity)
   at Microsoft.SqlServer.Types.GeographyUnionAggregate.Terminate()
.

And I'm able to produce it with the following code:

select
    *
from
    tblGeography
    cross apply
    (
        select
            geography::UnionAggregate(ZC_Geography) as NewGeom
        from
            tblGeographyDetail
            join tblZipCode on ZC_Zip_Code like GD_Zip_Code
        where
            GD_GEO_ID=GEO_ID
    ) a
where
    GEO_ID=2

I've found that this issue seems to be a problem with the number of rows I was attempting to aggregate.  I get errors when aggregating over about 5,000 rows, but don't get errors (well, the same error, anyway) when aggregating less than that.

My solution ended up being to segment and aggregate the rows using ROW_NUMBER, then aggregate those groups, as in the following:

select
    geography::UnionAggregate(GroupGeography)
from
    (
        select
            geography::UnionAggregate(ZC_Geography) as GroupGeography
        from
            (
                select
                    *,
                    ROW_NUMBER() OVER(PARTITION BY 'dummy' ORDER BY ZCG_Zip_Code) / 1000 as ZipGroup
                from
                    tblZipCode
            ) a
        group by ZipGroup
    ) groups


Identifying the normal form that a certain relation complies with I have a little problem with a normalization exercise. I have the following relation:

R = {L,K,D,S,O,W}

which presents the functional dependencies (FD) shown below:

F = { {D,S,K} -> {O},
      {O} -> {K},
      {O,L} -> {W}
      {W,L,K} -> {O},
      {D,S,W} -> {L}}

I have to determine what normal form (NF) is satisfies. So I started from determining the candidate keys; they are:

{D,S,O,L}

{D,S,L,K}

{D,S,K,W}

{D,S,O,W}

Then, I worked from-top-to-bottom (not sure if it is good strategy).

This relation isn't in BCNF because the left side of every FD does not contain key(s) e.g. {O} -> {K}
My definition for 3NF was that, for every nontrivial, simple FD, the left side contains a key or the right side is part of a key (or it is a key). Everything matches and I thought that the relation R is in 3NF.

Then I look on the conditions to be in 2NF and saw that:

This relation contains partial dependency e.g. {O} -> {K}
This relation contains transitive dependency.

Transitive dependency is for every FD X -> A, X is a subset or superset of the key (is that right, but if X is a subset of the key then this is a partial dependecy)?

This dependency doesn't work for me {W,L,K} -> {O} - this is transitive. So, is relation R in 1NF? This is something weird, either I have a bad definition of 3NF or this relation is in 3NF... but it isn't in 2NF, which is impossible. What's wrong?

All attributes are prime, they belong to at least one candidate key.

Therefore, the relation is - trivially - in 3NF.

If you look at the requirements for both 2NF and 3NF, the exceptions start with "every non-prime attribute ...". Since the relation has 0 non-prime attributes, these requirements are trivially covered.

About your analysis, the first parts are OK (finding the candidate keys, that the relation is not in BCNF and that it is in 3NF).

The last part, about 2NF, shows confusion. Check again the 2NF definition and how do you define partial dependency and transitive dependency.


SQL Server - Linked Server error - Could not connect to server XYZ because Server being used: SQL Server 2012 SP3
I just received a strange error (strange because it worked earlier)

could not connect to server LNK_SRV because sa is not defined as a remote login

Not sure why we are receiving the error.

The sys.servers says is_linked = 0 for LNK_SRV - not sure why.
The following returns results

select top 10 * from LNK_SRV.DBName.dbo.TblName

But the procedures are failing

exec LNK_SRV.DBName.dbo.ProcName

It was working fine before we dropped the linked server and re-added it.
Also, the LNK_SRV is being used as for replication as well.

Note: I am aware sa is bad. It will be taken care soon.

The linked server was also used for replication.
The order of Subscribe and Create linked server was the issue.

Reproduce the error:
1.  Drop the subscription
2.  Drop the linked server
3.  Configure the Subscription first
4.  Linked Server next
5.  Testing
- a.    Select will return results
- b.    Exec proc result in error

Fix:
1.  Drop the subscription
2.  Drop the Linked Server
3.  Create the Linked Server first
4.  Configure the Subscription next
5.  Testing
- a.    Select will return results
- b.    Exec proc will succeed

Also, executing the following on Linked Server worked as well.

--Run this statement on LNK_SRVR
exec sp_AddRemoteLogin 'CurrServer','sa'


How to get the column name having a particular value from a SQL Server table? I have a value (say 5) but I do not know in which column of the table it is stored in. I want to get the column name which has this value. Please help me with this.

This can be achieved with a CASE..WHEN combination, with one WHEN for each column of the table in question.  I use a temporary table to demonstrate the technique but it works just as well for normal tables.

drop table #T;

create table #T(PK int, c1 int, c2 int, c3 int);

insert #T values (1,1,3,5);

select
    PK
    ,case 5  -- The particular value you're looking for
        when c1 then 'c1'
        when c2 then 'c2'
        when c3 then 'c3'
        -- one WHEN for each column of the table
        else 'not found'
    end
from #T;

Most often the CASE is written with the column name given and various values listed.  I've inverted this so multiple columns can be checked against a single, known value.  This could be achieved just as well using the longer form of CASE i.e.

CASE
    WHEN C1 = 5 THEN 'C1'
    WHEN C2 = 5 THEN 'C2'
...

A second option is UNPIVOT, which turns columns into rows.  It is well documented in Books Online, though it may take some concentration to follow the examples properly.  There are several questions on SO and dba.SE on this topic if you want examples.  The output from an UNPIVOT will look something like this:

PK  ColName  ColValue
 1  C1       1
 1  C2       3
 1  C3       5

You can query this with a simple WHERE ColValue = 5.

I have difficulty understanding what use you have for this as each column in a table should have different meaning.  A value of "5" in the "Age" column is very different to "5" in the "Shoe Size" column.  They cannot be meaningfully compared or combined.  Perhaps you have a one-to-many relationship hidden in your schema i.e. you have a table with columns named, say, Value1, Value2, Value3 etc.  If so I'd suggest you re-examine you design if at all possible.


Optimization by replacing subquery with named subquery having INLINE hint Let's have these two tables:

create table table_x(
  x_id varchar2(100) primary key
);

create table table_y(
  x_id varchar2(100) references table_x(x_id),
  stream varchar2(10),
  val_a number,
  val_b number
);

create index table_y_idx on table_y (x_id, stream);

Suppose we have millions of rows in each table and table_y contains from 0 to 10 rows per each x_id.

The queries in the following examples return 200 rows by filter substr(x_id, 2, 1) = 'B'.

It's required to optimize the query:

QUERY 1

select
    x.x_id,
    y.val_a,
    y.val_b
  from table_x x

  left join (select
                 x_id,
                 max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
                 max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
               from table_y
              group by x_id
   ) y on x.x_id = y.x_id

 where substr(x.x_id, 2, 1) = 'B'; -- intentionally not use the primary key filter

------
PLAN 1
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 1 |   HASH JOIN OUTER      |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 2 |    TABLE ACCESS FULL   | TABLE_X |   10000 |   120000 |   669 | 00:00:09 |
|   3 |    VIEW                |         |   10692 |  2437776 | 22029 | 00:04:25 |
|   4 |     SORT GROUP BY      |         |   10692 |   245916 | 22029 | 00:04:25 |
|   5 |      TABLE ACCESS FULL | TABLE_Y | 1069200 | 24591600 | 19359 | 00:03:53 |
----------------------------------------------------------------------------------
* 1 - access("X"."X_ID"="Y"."X_ID"(+))
* 2 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

There's a way of significant optimization, so QUERY 2 returns rows 2-3 times faster than QUERY 1. The INLINE hint is сritically important, because without it the second performs as slow as the first one.

QUERY 2

with
  table_y_total as (
    select --+ INLINE
        x_id,
        max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
        max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
      from table_y
     group by x_id
)
select
    x.x_id,
    (select val_a from table_y_total y where y.x_id = x.x_id) as val_a,
    (select val_b from table_y_total y where y.x_id = x.x_id) as val_b
  from table_x x
 where substr(x.x_id, 2, 1) = 'B';

------
PLAN 2
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes  | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             | 10000 | 120000 |  669 | 00:00:09 |
|   1 |   SORT GROUP BY NOSORT         |             |     1 |     19 |  103 | 00:00:02 |
|   2 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   1900 |  103 | 00:00:02 |
| * 3 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
|   4 |   SORT GROUP BY NOSORT         |             |     1 |     20 |  103 | 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   2000 |  103 | 00:00:02 |
| * 6 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
| * 7 |   TABLE ACCESS FULL            | TABLE_X     | 10000 | 120000 |  669 | 00:00:09 |
-----------------------------------------------------------------------------------------
* 3 - access("X_ID"=:B1)
* 6 - access("X_ID"=:B1)
* 7 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

Since the first query uses less code duplication I would prefer to keep it.

Is there a hint or another trick to meet the following conditions both?

keep the first query code (QUERY 1)
force optimizer use the second plan (PLAN 2)

TL;DR Rewriting the query is not going to get you anywhere here. Use functional indexes to cut the cost of accessing 200 rows out of your millions.

Ok, replacing subquery with CTE and INLINE hint has nothing to do with optimization. Increased readablity? Sure. Performance? Hardly.

The plans differ, because your queries are not really equivalent. First query performs join. Second query has separate subqueries in SELECT clause, and for optimizer that is obviously different.

To make them "sort of equivalent" you would have to join resultsets in Query 2.

with
  table_y_total as (
    select --+ INLINE
        x_id,
        max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
        max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
      from table_y
     group by x_id
)
select
    x.x_id,
    y.val_a,
    y.val_b
from table_x x
left outer join table_y on y.x_id = x.x_id
where substr(x.x_id, 2, 1) = 'B';

No "code duplication" as well. The plan would probably match the one for the first query.

What happens in your case is you have two tables with table_x having 100 times fewer rows than table_y. By inlining the access to table_y in SELECT you leave optimizer no choice but use index. Accessing 1% of rows via index is usually faster than a fullscan. No surprises here.

However, you specified the scale of millions of rows in each table. I don't know how the cardinalities of X and Y are going to correlate, but we don't really need to know.

Fullscans do not scale well.

On the other hand, whether you only have 10 thousand rows or 10 milllion rows you always need only 200 that match the substr(x_id, 2, 1) = 'B' criterion, right?

Functional indexes will definitely work here.

create index table_x_2nd_char_of_pk_idx on table_x (
  substr(x_id, 2, 1) ASC);

Once you dropped the cardinality to these 200 rows, accessing table_y via its FK is a piece of cake.

Ok, I've added this part after "fullscan on x is not a problem". I'm sure it is, but you can deal with it separately.

Whether you index access predicates or not you can make sure access is isolated and cardinality is low enough when the bigger table is joined.

with
  table_x_filtered as (
    select x.x_id
    from   table_x x
    where  substr(x.x_id, 2, 1) = 'B'
    and    rownum > 0) -- well, better than hints, right?
select
    x.x_id,
    max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
    max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
  from table_x_filtered X
  left outer join table_y y on y.x_id = x.x_id
 group by x.x_id

If table_x is going to stay really small forever, the optimizer should choose to fullscan it first, apply filter and then join. If not, well, you can always use leading, as @panick suggests, but without proper indexing it's going to get slower and slower anyway.


Workaround to implement Minimum Degree of Parallelism Please correct me if I am mistaken but as I understand it, when a query is being scheduled for execution, the engine takes into account the number of available free threads and will adjust the query plan accordingly.

For example, if a machine is highly loaded such that only a single thread is free, a query that normally runs using multiple threads on an unloaded machine could be executed as single threaded.

Is there a way to dynamically query the number of free threads available so that a particular critical query (that runs unacceptably as MaxDop < X) could be delayed (while/waitfor) for execution until a minimum free thread count is available?

Update: A starting point based on Kin's answer:

This seems to follow a simple load test but never returns all idle for some reason.  I know nothing about this table.

select count(*) as Cpus,
       sum(IsIdle) + 1 as IdleCpus  -- +1 since current query should be excluded
  from
     (
        select Cpu_id,
               min(convert(int, is_idle)) IsIdle
          from sys.dm_os_schedulers
          group by Cpu_id
     ) q
   ;

There is no such concept like minimum degree of parallelism.

SQL Server has Maximum degree of parallelism (max DOP) and cost threshold of parallelism. These 2 settings are used by sql server's cost based optimizer on the number of threads to use to run queries.

  if a machine is highly loaded such that only a single thread is free, a query that normally runs using multiple threads on an unloaded machine could be executed as single threaded.

If a machine is heavily loaded or a run away query monopolizes the server, then you will run into thread starvation where in you will exhaust the available worker threads. You should adjust the max dop setting to a sensible value and test it out.

  Is there a way to dynamically query the number of free threads available so that a particular critical query (that runs unacceptably as MaxDop < X) could be delayed (while/waitfor) for execution until a minimum free thread count is available?

Below query will help you will provide information about the system tasks that have spawned the additional threads

SELECT  s.session_id, r.command, r.status,
r.wait_type, r.scheduler_id, w.worker_address,
w.is_preemptive, w.state, t.task_state,
t.session_id, t.exec_context_id, t.request_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks AS t
ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers AS w
ON t.worker_address = w.worker_address
WHERE s.is_user_process = 0;

Alternatively, you can check sys.dm_os_waiting_tasks for any THREADPOOL related waits. Also, sys.dm_os_scheduler will tell you the current_workers_count for each scheduler.

You can have some complex logic build in to check for current worker count and then just have a waitfor delay x where x is the delay that you want. In my opinion, it will be an overkill to unnecessarily complicate things.

The best is to tune maxdop, cost threashold of parallelism along with making sure that the stats are up-to-date and gathering the wait stats for your instance. In short, baseline your server and see if you spot any anomalies and then start your tuning exercise.


Filegroup with multiple files: odd behaviour when moving stuff in I am currently migrating databases from SQL Server 2008 (SP4) to SQL Server 2017 (CU3). A significant change is that all filegroups have two data files after migration. To accomplish this task I restore a backup, add the new filegroup with two equally sized files as well as the same autogrowth settings and transfer the data by using the following syntax:

CREATE UNIQUE CLUSTERED INDEX <PK of the table> ..... WITH (DROP_EXISTING = ON ,...) ON <new Filegroup>

Unfortunately I also have to move some LOBs then things get a little more complicated:

Add a partition scheme and partition function (basically with the same target)
CREATE the clustered index (WITH DROP_EXISTING=ON) on the new partition scheme
CREATE the clustered index (WITH DROP_EXISTING=ON) on the new filegroup
DROP the partition scheme and function

This technique is described by Kimberly Tripp here and goes back to Brad Hoff.

Thank you for staying tuned and now to my question:

How much free space is needed in a filegroup by rebuilding an index like this?

I'll give you an example:

I have one LOB table which is 220GB in size (according to total pages in sys.allocation_units divided by 128 divided by 1024).
Presized the two files in my new (empty) filegroup (to host just that table) by 220/2 =  110 GB each with filegrowth = 0.
Tried to transfer the table using the technique described above but got error message

  Could not allocate space for object 'xyz'.'xyz_pk' in database 'abc' because the 'def' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Increased the filesize to 112 GB each...tried again and got the same error message
Finally turned on autogrowth for the files and the process finished successfully

however each file then was 220 GB in size leaving 50% of free space within each file.

Here is an output of the suggested diagnostic query

The file size adds up to 227,22 GB.

Up to now I do not know any other remedy as to perform DBCC SHRINKFILE to get rid of the ridiculous high amount of free space. However that's not something I am particulary proud about...it takes ages potentially leaves corruption etc.etc.

Can you help me understanding why SQL Server allocates so much free space while afterwards happily filling my two files proportionally?

I'll try to prepare a demo afterwards...sorry but I am short on time now and perhaps some of you experts do already know the reason for this.

Thanks in advance for your help

Martin

First of all thank you guys for helping me to get on track with your comments.

I have now worked through an example and have a better understanding what's going on.

The problem arises with moving LOB-Data (such as VARCHAR(MAX), XML and so on) to another filegroup.
When you rebuild a clustered index on another filegroup the LOB-Data stays at it's former place (set by the TEXTIMAGE ON command in the CREATE TABLE statement).

One classic way to move the LOB-Data is to create a second table with the same structure in the new filegroup, copy data over, drop the old table and rename the new one. However this brings in all sorts of possible issues like lost data, invalidated data (because of missing check constraints) and error handling is quite tough. I have done this for one table in the past but IMHO it doesn't scale well and consider the nightmare of having to transfer 100 tables and you got errors for table 15, 33, 88 and 99 to fix.

Therefore I use a well-known trick regarding partitioning: As described by Kimberly Tripp LOB-Data does move to the new filegroup when you put partitioning on it. As I do not plan to use partitioning in the long run but just as a helper for moving that LOBs, the partition scheme is quite dull (throwing all partitions into one filegroup): I don't even care, which partition the data is on as I just want to get them moved. Actually this technique and the implementation is not invented by myself...I use a formidable script by Mark White. My mistake was to not fully understand what this script does and what the implications are....which I have now:

For LOB-Data it is necessary to rebuild (or recreate) the table (mostly the clustered index) twice: first with putting partitioning on it and second with removing the partitioning. Whether you use SORT_IN_TEMPDB=ON or not this results in having to provide the space of the original data TWICE: if your original table has 100MB, you need to provide 200MB for the operation to succeed. At the beginning I was quite puzzled, ending up with my new data files which had a lot of free space after the operation was finished.

Now I accepted that I can't cheat around avoiding the free space. However I could avoid the necessity to shrink files afterwards. Therefore my solution is to do the first rebuild on a temporary filegroup and the second rebuild (removing partioning) on the destination filegroup. The temporary filegroup can be removed afterwards (if hopefully I don't hit the error message "The filegroup cannot be removed" (have a look at my question here) anymore.

Thanks for reading and your help

Martin

Here is a repro script for my problem which includes the solution I have come up for it:

    /*============================================================================
  Adapted the following file published by sqlskills to demonstrate filegrowth
  after partitioning for StackOverflow Question.

  Martin Guth, 02.02.2018

  File:     MovingLOBData.sql

  Summary:  Because 2012 supports online index rebuilds - even with LOB.
            You might think this means you can move LOB data around
            (one of the VERY cool things you can do with IN_ROW data to
            actually move it). However, the behavior of LOB data is NOT
            necessarily intuitive. This script will show you how/why/what!

  SQL Server Versions: SQL Server 2012
------------------------------------------------------------------------------
  Written by SQLskills.com

  (c) SQLskills.com. All rights reserved.

  For more scripts and sample code, check out
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

SET NOCOUNT ON
go

USE master
go

CREATE DATABASE [TestLOB]
 CONTAINMENT = NONE
ON  PRIMARY
(   NAME = N'TestLOBPrimary'
    , FILENAME = N'U:\DB_DATA\TestLOBPrimary.mdf'
    , SIZE = 100MB , FILEGROWTH = 1024KB ),

FILEGROUP [FG1]
(   NAME = N'FG1File1'
    , FILENAME = N'U:\DB_DATA\FG1File1.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),
(   NAME = N'FG1File2'
    , FILENAME = N'U:\DB_DATA\FG1File2.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),

FILEGROUP [FG2]
(   NAME = N'FG2File1'
    , FILENAME = N'U:\DB_DATA\FG2File1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'FG2File2'
    , FILENAME = N'U:\DB_DATA\FG2File2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),

FILEGROUP [tempLOB]
(   NAME = N'tempLOB1'
    , FILENAME = N'U:\DB_DATA\templob1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'tempLOB2'
    , FILENAME = N'U:\DB_DATA\templob2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB )

LOG ON
(   NAME = N'TestLOBLog'
    , FILENAME = N'U:\DB_DATA\TestLOBLog.ldf'
    , SIZE = 10MB , FILEGROWTH = 10MB)
GO

USE TestLOB
go

ALTER DATABASE TestLOB
MODIFY FILEGROUP FG1 DEFAULT
go

--DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go

INSERT INTO dbo.TestLobTable
(
    c2,
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000)
)
go 1000

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
go

sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'

go

-- size is roughly 40 MB: 1,000 rows; 39.2MB; 31.3MB LOB

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  2.875000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500

--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go

CREATE PARTITION SCHEME PS_TestLobTable
AS PARTITION PF_TestLobTable
TO ( fg2, fg2 )
go

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB= OFF)
ON PS_TestLobTable (c1)
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2688    21.000000   19.750000
FG2File2    2688    21.000000   19.687500

--> now Filegroup 2 has roughly 40 MB data...interestingly the create index would fail if having 2*20MB capacity available but would pass at 2*21MB
*/

-- try to recreate the index again to get rid of partitioning
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB= OFF)
ON [FG2]
go

/* error message 1105
Could not allocate space for object 'dbo.TestLobTable'.'TestLobTableCL' in database 'TestLOB' because the 'FG2' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

--> makes sense because no free space available in filegroup with partitioned clustered index already present
*/

ALTER DATABASE TestLOB MODIFY FILE (NAME = N'FG2File1', SIZE=41MB);
ALTER DATABASE TestLOB MODIFY FILE (NAME = N'FG2File2', SIZE=41MB);

-- rebuild again without sort in tempdb
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON,  SORT_IN_TEMPDB= OFF)
ON [FG2]
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5248    41.000000   19.750000
FG2File2    5248    41.000000   19.625000

--> now the files of FG2 have 50% free space left
*/

-- try to shrink with truncateonly
DBCC SHRINKFILE('FG2File1', TRUNCATEONLY);
DBCC SHRINKFILE('FG2File2', TRUNCATEONLY);

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5048    39.437500   19.750000
FG2File2    5040    39.375000   19.625000

--> no significant effect...still almost 50% free space
*/

-- recreate the table
DROP PARTITION SCHEME PS_TestLobTable;
DROP PARTITION FUNCTION PF_TestLobTable;

DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go

INSERT INTO dbo.TestLobTable
(
    c2,
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000)
)
go 1000

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
go

sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'
GO

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.125000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    5048    39.437500   0.062500
FG2File2    5040    39.375000   0.062500

--> data on filegroup 1 again... move them to filegroup2 this time with SORT_IN_TEMPDB
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go

CREATE PARTITION SCHEME PS_TestLobTable
AS PARTITION PF_TestLobTable
TO ( fg2, fg2 )
go

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON )
ON PS_TestLobTable (c1)
go

-- removing partitioning on table
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON )
ON [fg2]
go

-- now try to shrink with truncateonly
DBCC SHRINKFILE('FG2File1', 20,TRUNCATEONLY);
DBCC SHRINKFILE('FG2File2', 20,TRUNCATEONLY);

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5376    42.000000   19.687500
FG2File2    5376    42.000000   19.687500

--> no significant effect...still almost 50% free space
*/

--- retry with separate filegroup

CREATE DATABASE [TestLOB]
 CONTAINMENT = NONE
ON  PRIMARY
(   NAME = N'TestLOBPrimary'
    , FILENAME = N'U:\DB_DATA\TestLOBPrimary.mdf'
    , SIZE = 100MB , FILEGROWTH = 1024KB ),

FILEGROUP [FG1]
(   NAME = N'FG1File1'
    , FILENAME = N'U:\DB_DATA\FG1File1.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),
(   NAME = N'FG1File2'
    , FILENAME = N'U:\DB_DATA\FG1File2.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),

FILEGROUP [FG2]
(   NAME = N'FG2File1'
    , FILENAME = N'U:\DB_DATA\FG2File1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'FG2File2'
    , FILENAME = N'U:\DB_DATA\FG2File2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),

FILEGROUP [tempLOB]
(   NAME = N'tempLOB1'
    , FILENAME = N'U:\DB_DATA\templob1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'tempLOB2'
    , FILENAME = N'U:\DB_DATA\templob2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB )

LOG ON
(   NAME = N'TestLOBLog'
    , FILENAME = N'U:\DB_DATA\TestLOBLog.ldf'
    , SIZE = 10MB , FILEGROWTH = 10MB)
GO

USE TestLOB
go

ALTER DATABASE TestLOB
MODIFY FILEGROUP FG1 DEFAULT
go

--DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go

INSERT INTO dbo.TestLobTable
(
    c2,
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000)
)
go 1000

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
go

sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'

go

-- size is roughly 40 MB: 1,000 rows; 39.2MB; 31.3MB LOB

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  2.875000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500

--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go

CREATE PARTITION SCHEME PS_TestLobTable
AS PARTITION PF_TestLobTable
TO ( tempLOB, tempLOB )
go

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON)
ON PS_TestLobTable (c1)
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500
tempLOB1    2560    20.000000   19.687500
tempLOB2    2560    20.000000   19.687500

--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON)
ON [FG2]
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2560    20.000000   19.625000
FG2File2    2560    20.000000   19.750000
tempLOB1    2560    20.000000   0.062500
tempLOB2    2560    20.000000   0.062500

--> data successfully moved to fg2, tempLOB empty
*/
DROP PARTITION SCHEME PS_TestLobTable
DROP PARTITION FUNCTION PF_TestLobTable

ALTER DATABASE TestLOB REMOVE FILE tempLOB1;
ALTER DATABASE TestLOB REMOVE FILE tempLOB2;
ALTER DATABASE TestLOB REMOVE FILEGROUP tempLOB;

/*
    summary:
    - Moving LOB Data with the help of partitioning results in twice the space needed of the original data for temporary rebuilds.
    - To avoid problematic and long running shrinking of database files it's best to use a different filegroup for the first rebuild as this can be easily removed afterwards.
*/


pt-table-checksum toolkit for few databases from file I've got a question. I'm doing a simple script right now and it isn't working like I want.

    #!/bin/bash

    cat /etc/backup.conf | egrep -v "(^#.*|^$|^--.*)" > /tmp/databases

    while read line; do
    pt-table-checksum [options] --databases $line --quiet
    done < /tmp/databases

    rm /tmp/databases

As you see I put names of databases to file /tmp/databases. Then I want to use pt-table-checksum for every database in that file line by line. This script works but only for first database name in file. Then it stops.

Any ideas?

I would use a 'for' loop for this, which I have found easier to use for things like this. Often, either line break, spaces, or combinations of those can cause issues with your method.
Also, remove the --quiet from your command line to see the errors that pt-table-checksum may be reporting.

#!/bin/bash

for db in `cat /etc/backup.conf | egrep -v "(^#.*|^$|^--.*)"`
do
    pt-table-checksum [options] --databases $db --quiet
done

(Hope this reads ok, have answered on my phone!)


Question about RMAN-01009，RMAN-01007 After i created the tablespace named "rman" and created the user named"rman" and accredited to the user.

I connected to the recovery catalog database successfully.

But when i got into the next step.It mentioned ERROR.

Anyone could help me?
Thanks

Connecting to the Target Database and Recovery Catalog

The syntax will be below mention like that

rman TARGET / CATALOG rman/cat@catdb;

where

rman is Owner of the recovery catalog having RECOVERY_CATALOG_OWNER privilege.

cat is The password for user RMAN specified in the recovery catalog's orapwd file.

catdb is The net service name for the recovery catalog database.

for your further ref Connecting to Databases with RMAN


IN clause not working on mysql 8.0.17 I was on mysql:8.0.15 and I updated to mysql:8.0.17

On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect
On 8.0.17 the same query with same data return 0 results.

I thought it was our application, so I downgraded back to 8.0.15 and the query is working again...

I noticed ... and (col1, col2) in (('a', 'b'),('a', 'b')) is working in 8.0.17

I tried to search mysql bug list, but I can't find any information... In the changelog I see they optimized WHERE IN in 8.0.17, but it should not break my use case.

  The optimizer now transforms a WHERE condition having NOT IN (subquery), NOT EXISTS (subquery), IN (subquery) IS NOT TRUE, or EXISTS (subquery) IS NOT TRUE internally into an antijoin, thus removing the subquery. This is similar to the existing IS NULL (Not exists) outer join optimization; see EXPLAIN Extra Information, for further information.
  In addition, the semijoin materialization can now be used for a WHERE condition having IN (subquery) IS TRUE, or EXISTS (subquery) IS TRUE, or when the IN condition belongs to a left join such as SELECT * FROM t1 LEFT JOIN t2 ON t2.x IN (SELECT * FROM t3). See Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations.
  Also as a result of this work, MySQL is now able to simplify a co

It seems too big to be a bug in mysql. Am I missing something ?

Here is the full query

select `t1`.`user_id`    as `user_id`,
       `t1`.`created_at` as `created_at`,
       `t1`.`updated_at` as `updated_at`,
       `t3`.`node_id`    as `t3node_id`,
       `t3`.`node_type`  as `t3node_type`,
       `t2`.`node_id`    as `t2node_id`,
       `t2`.`node_type`  as `t2node_type`
from `table1` as `t1`
         inner join `table2` as `t2` on `t1`.`set_id` = `t2`.`set_id` and `t1`.`set_type` = `t2`.`set_type`
         left join `table2` as `t3`
                   on `t2`.`node_type` = 3 and `t3`.`set_type` = 5 and `t3`.`set_id` = `t2`.`node_id`
         left join `table3` as `ts` on COALESCE(t3.node_type, t2.node_type) = `ts`.`cache_node_type` and
                                                 COALESCE(t3.node_id, t2.node_id) = `ts`.`cache_node_id`
where `t1`.`tenant_id` = '825773475713154048'
and (`t1`.`user_id`, `t2`.`node_id`) in ( ('825773502556700160', '825773839074099712'))

On 8.0.17 I get 0 results. On 8.0.15 it works.

If, on 8.0.17, I replace the last line by and (t1.user_id, t2.node_id) in ( ('825773502556700160', '825773839074099712'), ('825773502556700160', '825773839074099712')) (notice it's the same tuple, two times) I get results.
It works with this and (t1.user_id, t2.node_id) = ('825773502556700160', '825773839074099712')
I tried with a simple query SELECT * FROM test.test as t1 WHERE (t1.col1,t1.col2) IN (('a','b')); it works on 8.0.17.

Have you tried a simple IN clause other than the one in your code?  If not, I would write a couple simple ones and try them to see if they run.  If they run successfully, it's either you copied it incorrectly, OR it's got to be something specific with how your code is using the IN statement.

If no IN statements run, something was changed, either by design or it's a bug.


Unlock HR user in Oracle 19c/18c/12c? While trying to unlock the HR user, I am getting an error saying, “user HR does not exist”.

Unlocking sample accounts like HR in Oracle 19c/18c/12c

Edit tnsnames.ora  file located at %db_home%\network\admin

Add TNS ORCLPDB like below-highlighted block

Open CMD as Administrator and execute below command

C:\>lsnrctl reload

Open CMD and execute SQLPlus with below command to login as SYSDBA

C:\WINDOWS\system32> sqplus / as sysdba

Run below SQL commands to Unlock your Schema

  ALTER SESSION SET container = ORCLPDB;

Check if the pluggable database is opened ?

SELECT name, open_mode FROM v$pdbs;

  To open your pluggable database Skip If Already open

ALTER PLUGGABLE DATABASE open;

Unlock HR user

ALTER USER hr IDENTIFIED BY <your_password> ACCOUNT UNLOCK;
conn
username hr@orclpdb
password <your_password>
Connected.

Connected means you can access HR schema lets start

  Now you will be able to access HR schema

select * from tab;


An unknown error occurred while processing the request on the server I have an asp.net 4.0 application with a button that opens a SSRS report.  This works fine on my development machine and I can open the report directly with report manager but when I try to access the report on the production machine I get this error

Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; CMDTDF; BRI/1; .NET4.0C; InfoPath.3; .NET4.0E)
Timestamp: Sat, 1 Dec 2012 21:35:46 UTC

Message: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500
Line: 2
Char: 74741
Code: 0
URI: http://intranet/webapps/TestRecordedOnlyIncidents/ScriptResource.axd?d=ZBsMSL9jIyW8I9p1LwFOBagb0X-bFxrU28WowdY5TbC9ZankzHyooPpti5W0JT6DLsOOmhf_2t5kU1g4r1F40RiMcHtiuYqff50r1VaQMwtZ9CW6zRVLnDMJYHiVvrNZ0&t=ffffffffbad362a4

I've searched the web for a solution and have found a half a dozen but none have worked for me.  Here is my code:

    Dim r = New ServerReport
    r.ReportServerUrl = New Uri("http://vm-intranet/ReportServer")
    r.ReportPath = "/RecordedOnlyIncidents/Reports/AllIncidentsWithParameters"
    Dim EmployeeNumber As String = IIf(txtSearchEmployeeNumber.Text = "", Nothing, txtSearchEmployeeNumber.Text)
    Dim IncidentType As String = IIf(ddlSearchIncidentType.SelectedItem.Text = "", Nothing, ddlSearchIncidentType.SelectedItem.Text)
    Dim BeginningDate As String = IIf(txtBeginningDate.Text = "", Nothing, txtBeginningDate.Text)
    Dim EndingDate As String = IIf(txtEndingDate.Text = "", Nothing, txtEndingDate.Text)
    Dim parmEmpNumEmployeeNumber As New ReportParameter("EmployeeNumber", EmployeeNumber)
    Dim parmIncidentType As New ReportParameter("IncidentType", IncidentType)
    Dim parmBeginningDate As New ReportParameter("BeginningDate", BeginningDate)
    Dim parmEndingDate As New ReportParameter("EndingDate", EndingDate)
    Dim parameters(3) As ReportParameter
    parameters(0) = parmEmpNumEmployeeNumber
    parameters(1) = parmIncidentType
    parameters(2) = parmBeginningDate
    parameters(3) = parmEndingDate
    r.SetParameters(parameters)
    Dim warnings As Warning() = Nothing
    Dim streamids As String() = Nothing
    Dim mimeType As String = Nothing
    Dim encoding As String = Nothing
    Dim extension As String = Nothing

    Dim reportOutput As Byte() = r.Render("PDF", Nothing, mimeType, encoding, extension, streamids, warnings)
    Dim path As String = System.AppDomain.CurrentDomain.BaseDirectory
    Dim stream As New IO.FileStream(path & "/MyReport.pdf", IO.FileMode.Create)
    stream.Write(reportOutput, 0, reportOutput.Length)
    stream.Close()
    Dim Script As String = "<script type='text/javascript'> win=window.open("""",""_blank"",""resizable=Yes,height=600"");win=win.document;win.write(""<style>body{margin:0px;}</style>"");win.write(""<title> Incidents Report</title>"");win.write(""<iframe src='MyReport.pdf' style='width:100%;height:100%;'></iframe>"");</script>"
    ScriptManager.RegisterStartupScript(Me, Me.GetType(), "OpenReport", Script, False)

Any suggestions would be greatly appreciated.

I found the answer, the wrong version of the Report Viewer was installed on the server.


Identical tables, identical query, completely different execution times We have two tables with identical columns and indexing (No indexing at all, basically). We run the same query, which in case of the original table takes 5 seconds to run; in the case of the new table we let it run for 30 minutes and then killed the query.

We updated statistics, but that had no result. We tried a rebuild of the new table to see if defragmenting it would help, but that had no effect either.

On a hunch, we then exported both tables to the same database, in order to see whether or not anything would change there, but we have the exact same results.

I'm kind of baffled how this could be. To make things even more interesting, the original table contains more data than the new table, which should theoretically mean that the new table should finish the query faster.

Does anyone have a possible explanation? I've worked for years as a DBA (though not for a few years now), and frankly, I'm baffled as to why this could possibly happen.

In response to some of the comments, here is the table definition in question:

CREATE TABLE [dbo].[Fact_SubscriptionDetail_Test](
[CustomerSellTo_Key] [int] NULL,
[CustomerContactSellTo_Key] [int] NULL,
[CustomerRefTo_Key] [int] NULL,
[WebAuthUser_Key] [int] NULL,
[Country_Key] [int] NULL,
[Date_Key] [int] NULL,
[SnapshotDate_Key] [int] NULL,
[Product_Key] [int] NULL,
[License_Key] [int] NULL,
[Subscription_Key] [int] NULL,
[SubscriptionTypeLostSeatsType_Key] [int] NULL,
[M_SubscriptionDetail_NewSeats] [int] NULL,
[M_SubscriptionDetail_WinbackSeats] [int] NULL,
[M_SubscriptionDetail_RenewedFlexSeats] [int] NULL,
[M_SubscriptionDetail_RenewedCommitSeats] [int] NULL,
[M_SubscriptionDetail_RenewedSeats] [int] NULL,
[M_SubscriptionDetail_ActiveSeatsEndMonth] [int] NULL,
[M_SubscriptionDetail_LostNonPaymentSeats] [int] NULL,
[M_SubscriptionDetail_LostGraceInactiveSeats] [int] NULL,
[M_SubscriptionDetail_LostOtherSeats] [int] NULL,
[M_SubscriptionDetail_LostSeats] [int] NULL,
[M_SubscriptionDetail_GrossBookings] [numeric](38, 20) NULL,
[M_SubscriptionDetail_ActiveFlexSeats] [int] NULL,
[M_SubscriptionDetail_ActiveCommitSeats] [int] NULL,
[M_SubscriptionDetail_ActiveSeats] [int] NULL,
[DateCreated] [datetime] NULL
    CONSTRAINT [DF__Fact_Subs__DateC__gtfhjCC]  DEFAULT (getdate())
) ON [DATA]

Data in the two tables is not entirely identical, but very similar in nature. The query we run on it ties to some other tables (primarily Data warehouse dimensions).

I defragged (which I agree, doesn't make much sense for a heap, but I figured it wouldn't and couldn't hurt) by simply running

ALTER TABLE Fact_SubscriptionDetail_Test REBUILD.

When we initially checked the query plan, it suggested adding some indexes on the Test table (but not the original, fast table). We also attempted adding a clustered index (PK) on the Test table, but neither that, nor the suggested index by the Execution Plan had any effect.

Here are the execution plans:

http://pastebin.com/XagvSxjj (Original table; fast)
http://pastebin.com/LSgCsvUe (New table; slow)

There is a difference in the execution paths, making me think the cardinality of the data in the original table is somewhat better. The original table contains some 1.4 mio rows (230 MB), of which 400k are processed by the query. The new table contains 400k rows (52 MB).

This is the full query: http://pastebin.com/bYiaGW1d (slightly edited to remove some sensitive stuff).

The value for "cost treshold for parallellism" on the server is 5.

...the original table contains more data than the new table, which should theoretically mean that the new table should finish the query faster.

If the execution plans were the same, this would most likely be true, but they're not. The number of rows expected (and the distribution of the data according to the statistics) affects the strategy chosen by the query optimizer.
Small table
When the heap table contains 398,399 rows, the optimizer chooses a serial plan using nested loops for all join operations except those directly affecting the heap table; those joins employ a hash join.

The complexity of the query is such that cardinality (row count) estimates are likely to be inaccurate, so the nested loop strategy ends up being a disaster. The optimizer considered a parallel plan, but rejected it as more expensive than the serial nested loops option.
Larger table
When the heap table contains 1,750,640 rows, changes in cost estimations mean the optimizer assesses that a parallel plan using hash joins and optimized bitmap filters extensively will be a better strategy.

This plan shape is much more resilient to cardinality estimation errors. The hash join build inputs may spill to disk, but the worst case is much better than executing an entire subtree a huge number of times (using nested loops).
Solution 1
If you know that a parallel plan with bitmap filters is the best choice in general, you could use a Plan Guide to enforce this, or encourage such a plan with an OPTION (HASH JOIN, MERGE JOIN) query hint. You may not always get parallelism if the tables are small, but performance should still improve (and be more predictable in general).
Solution 2
You could also explore breaking the query into simpler sections, initially along the lines of the Common Table Expression boundaries. Materializing sensible-sized intermediate results into temporary tables means:

Each part of the query can be optimized separately
The optimizer has statistics and accurate cardinality information
Additional indexes can be added to the temporary tables if proven to be beneficial
Redundant CTE evaluations can be skipped


Ola Hallengren Parameters for Index Rebuild/Reorganize on SharePoint I plan on using the IndexOptimize script by Ola Hallengren to replace the SharePoint 2013 On Premise Defragmentation Timer Job.

Anyone out there using it for this purpose? What params do you use and how? Here is what I put together to target the databases that the OOOTB Timer job would normally handle:

EXEC sp_msforeachdb
'if exists(select 1 from [?].sys.objects where name=''proc_DefragmentIndices'')

EXECUTE dbo.IndexOptimize
@Databases = ''?'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

'

I'd go with just updating stats.

I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats".

Also, running UPDATE STATISTICS gets you both index and columns stats updates.  Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update.  On large tables, that may be a while and not frequent enough.

SQL2014 and lower - Default sample size will not go parallel, only FULLSCAN will

SQL2016 - Default sample size can also go parallel
(Parallel Statistics Update)

The syntax I usually use (In this case we do FULLSCAN [StatisticsSample=100]):

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample=100

This way, you get all the benefit of updated statistics (which the optimizer will thank you for) without all the I/O thrashing and logging of physical index structure maintenance.

Additional reading on TF2371, which allows the stats update on large tables to be triggered at lower percentages.  If you're on SQL 2016, this is enabled by default.

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server


How to remove sysadmin from sa login in sql server I have a requirement wherein I need to remove sysadmin server role from sa login.
I created 1 more user and gave the sysadmin role to the user.
When I uncheck sysadmin from sa login it throws an error.
Cannot use the special principal sa error 15405.
Can anyone have any idea how to do this?

You cannot remove sysadmin from this login. The login with sid 0x01 (generally named "sa") is always sysadmin.
I assume that you have disabled sa in the first place, btw.
How about renaming sa to some other name?


When is a full table scan better than index scan? What are some scenarios (in SQL Server) in which a full table scan is better than an index scan?

When the

table is small enough there is no practical difference
statistically, you'd return most rows anyway

The 2nd case needs qualifying

An index scan will replace an index seek if an index is covering
An index seek or scan with many rows that requires key/bookmark lookups will be expensive and a table scan could be better

Finally

An index scan and a table scan are pretty much the same for clustered indexes


How to link customers and suppliers in a database Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier together if needed?

In the customers table I think you can add an extra column which would flag whether that customer is also a supplier.  So you could have a generic customer table (or you can name it contacts table), with a column used as a supplier flag/indicator (could be numeric: 1 or 0 or char: y or n or even boolean: true or false)


Join two tables based on Aggregate Function sum() result I have a small issue related to joining two tables based on the result of SUM().Following are my tables.

Customers Table

CUSTOMERID      NAME AGE   ADDRESS       PHONE
        1        AA  33    Some Address  123654789
        2        BB  35    Some Address  123654789
        3        CC  55    Some Address  987654789
        4        DD  55    Some Address  987654789
        5        EE  25    Some Address  365754789
        6        FF  20    Some Address  365754789

Orders Table

    ORDERID  CUSTOMERID ORDERAMOUNT
    C12335    3         50000
    F12336    3         12000
    C12337    3         5000
    C12338    1         3700
    C12339    2         1100

I display the customerID,Name,Address,Sum of orders of the customers whose Sum of ORDERAMOUNT is more than 50000.

However,since I cannot use an Aggregate function like SUM() after a WHERE clause,I am in trouble here.

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders FROM ORDERS GROUP BY CUSTOMERID
)O ON C.CUSTOMERID = O.CUSTOMERID;

The most progressive working query I could write was this and it does not evaluate the condition of Sum of ORDERAMOUNT is more than 50000.

Is there a way that I can evaluate that condition as well? A help is much appreciated. Thanks in advance :)

You cannot use SUM in a WHERE clause. However, you can use it in a HAVING

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders
    FROM ORDERS
    GROUP BY CUSTOMERID
    HAVING SUM(ORDERAMOUNT)>5000
)O ON C.CUSTOMERID = O.CUSTOMERID;


psql: SELECT * ... except one column I search for a simple way to select all columns except one in psql.

With psql I mean the interactive command line.

I would be happy with a tool that expands to * to a list of quoted column names. Then I could remove the column to remove by hand.

My question is just about the interactive usage of psql. It is not a duplicate of questions of people unhappy with the sql standard and who want to execute something like "select *-foo".

To get the list of columns in default order, without the bad column:
SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum)
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytable'::regclass
AND    NOT attisdropped  -- no dropped (dead) columns
AND    attnum > 0        -- no system columns
AND    attname <> 'bad_column'  -- case sensitive!

Or just WHERE  attrelid = 'mytable'::regclass if you trust the search path to resolve to the right schema.
quote_ident() adds double-quotes where necessary.
I asked the same question in 2007 on pgsql-general. It was Postgres 8.2 back then. Sweet memories ...
Related:

How to check if a table exists in a given schema


Suggestion required for Large Database Optimization and performance We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from available data. we've some concerns and require technical input and advices.

What impact we may see in future for very large table? Is there any limitation on number of records in one table or size of table?
What is the best way to index large data tables like this?

Any suggestion for optimization and performance of database is also appreciated.

SQL Server DENALI will have column store indexes but this won't come until the end of this year or early next year but from what I have seen so far (very little), it will definitely help.

In SQL Server partitioning is your best friend here. And to your question abt the limitation of # of rows in a table, it is limited only by storage space.

http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx

As tables grow, adding and updating the data will take some time and you may want to look at using minimal logging if you are using SQL Server 2008.

http://sqlcat.com/whitepapers/archive/2009/02/15/the-data-loading-performance-guide-2008.aspx

And for large tables, updating indexes frequently can take significant times and have seen cases where updating the statistics will give you a quick boost instead of frequent index rebuilds.


Why is ASC 100x faster than DESC and what can I do about it? I'm on the cusp of understanding this, but I don't understand the fix that I need. The following query takes about minute, give or take, to return 20 records:
explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" desC
LIMIT 20;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=74.950..41059.055 rows=20 loops=1)
   Buffers: shared hit=38524709 dirtied=10
   ->  Index Scan Backward using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=74.947..41059.022 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10
 Planning time: 0.450 ms
 Execution time: 41059.233 ms

Flipping the sort order makes it take about 60ms:
explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" asC
LIMIT 20;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=63.701..63.939 rows=20 loops=1)
   Buffers: shared hit=45783
   ->  Index Scan using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=63.698..63.933 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 67080
         Buffers: shared hit=45783
 Planning time: 0.426 ms
 Execution time: 63.971 ms

You can see that in the first one, it gets nasty right here:
         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10

Here are the indexes on the table (omitting ones that are not related to this query):
Indexes:
    "search_docket_pkey" PRIMARY KEY, btree (id)
    "search_docket_7a46e69c" btree (court_id)
    "search_docket_court_id_2d2438b2594e74ba_like" btree (court_id varchar_pattern_ops)

Stray thoughts:

The court column has very low cardinality. It has about 500 values across about 50M rows.

I guess I could add a descending index on court_id, and maybe that'd fix it, but that doesn't seem right.

Maybe I need a multi-column index here on search_docket.id and search_docket.court_id? Seems off.

Is there something better I should be doing here that would make the index work better?

Ideal for those queries would be in index on (court_id, id), and with the columns in that order.  It should be extremely fast in either direction.  And once you have it, you should be able to get rid of the plain index on court_id as it wouldn't be much good anymore.


Maybe I can update mysql version. If I will update how can I do it?

In MySQL 8+ the query will be

with
cte0 as ( select *,
                 lag(value) over (order by run_date) lg,
                 lead(value) over (order by run_date) ld
          from test),
cte1 as ( select *,
                 row_number() over (order by run_date) rn
          from cte0
          where value = 1
            and (lg = 0 or lg is null)),
cte2 as ( select *,
                 row_number() over (order by run_date) rn
          from cte0
          where value = 1
            and (ld = 0 or ld is null))
select cte1.id `min(run_date)`,
       cte2.id `max(run_date)`,
       cte2.seed - cte1.seed difference
from cte1,
     cte2
where cte1.rn = cte2.rn;

min(run_date) | max(run_date) | difference
------------: | ------------: | ---------:
            3 |             5 |          7
            9 |            13 |         84

db<>fiddle here


sp_blitzfirst not showing memory grant columns I saw several videos where the know procedure sp_blitzfirst shows the memory grant columns, I even saw it working like that, but on the latest version of the first responder kit, it doesn't show those columns, were those removed?

When I execute it with @expertmode=1 it just show on the messages tab:

Setting up configuration variables
Now starting diagnostic analysis
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries
Finished running investigatory queries
Waiting to match @Seconds parameter
Capturing second pass of wait stats, perfmon counters, file stats
Analyzing changes between first and second passes of DMVs
Analysis finished, outputting results

Do you perhaps need to use sp_BlitzCache instead?

In the source code for sp_BlitzFirst is the line:

  Run sp_BlitzCache @SortOrder = 'memory grant' to find queries with huge memory grants and tune them.

On July 14, 2016, the following changes were made (commit 62b6e9df):

  Put all of the what’s running now work inside an IF statement so it
  only runs if we’re in ExpertMode (to minimize executions, since this
  proc needs to go fast under load.) Cleaned up some of the “memory not
  granted” and “memory already granted” stuff to just be N/A since it can
  mean different things depending on whether the query needs memory.


Optimizing a query that gets the opening balance I have a query like this

select org, `Balance` from oss_collection_history och2
  where och2.`DateFrom` =
    (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org)

It works, but with my data set it takes about 4 seconds.

The explain output looks like this

id|select_type       |table|partitions|type|possible_keys                     |key|key_len|ref         |rows|filtered|Extra      |
--|------------------|-----|----------|----|----------------------------------|---|-------|------------|----|--------|-----------|
 1|PRIMARY           |och2 |          |ALL |                                  |   |       |            |6651|     100|Using where|
 2|DEPENDENT SUBQUERY|och3 |          |ref |Org,oss_collection_history_Org_IDX|Org|22     |ca1.och2.Org| 443|     100|Using index|

I tried adding the following indeices, but doesn't appear to improve the performance

CREATE INDEX `oss_collection_history_Charges_IDX3`
ON oss_collection_history (`DateFrom`, `org`);

CREATE INDEX `oss_collection_history_Charges_IDX4`
ON oss_collection_history (`DateFrom`);

CREATE INDEX `oss_collection_history_Charges_IDX6`
ON oss_collection_history (`DateFrom`, `org`, `Balance`);

I created a minimal, complete, and verifiable example for your query.

CREATE TABLE oss_collection_history
(
    org int
    , Balance int
    , DateFrom date
);

INSERT INTO oss_collection_history VALUES (1, 1, '2019-01-01');
INSERT INTO oss_collection_history VALUES (1, 2, '2019-01-02');
INSERT INTO oss_collection_history VALUES (2, 1, '2019-01-03');
INSERT INTO oss_collection_history VALUES (2, 2, '2019-01-04');

CREATE INDEX oss_collection_history_Charges_IDX2
ON oss_collection_history (org, DateFrom, Balance);

This query returns a single row-per-org, with the associated opening balance, using the index:

SELECT och1.org
    , och1.Balance
    , och1.DateFrom
FROM oss_collection_history och1
INNER JOIN (
    SELECT MIN(och2.DateFrom) AS MinDateFrom
        , och2.org
    FROM oss_collection_history och2
    GROUP BY och2.org
) t ON och1.org = t.org AND och1.DateFrom = t.MinDateFrom;

Results:

╔══════╦══════════╦════════════╗
║ org  ║ Balance  ║  DateFrom  ║
╠══════╬══════════╬════════════╣
║   1  ║       1  ║ 2019-01-01 ║
║   2  ║       1  ║ 2019-01-03 ║
╚══════╩══════════╩════════════╝

This fiddle for this is here.


You cannot simply use the difference operator -, because it does not allow multiple resulting rows:

  ERROR:  result of range difference would not be contiguous

So you need to create three rows and use the intersection operator *:

1st range unbounded up to lower bound
2nd range as given
3rd range upper bound to unbounded

WITH cte(r, val) AS (SELECT '[2010-01-01 14:10, 2010-01-01 14:30)'::tsrange, 15)
SELECT t.range * tsrange(NULL, lower(c.r), '(]') - c.r AS range, t.val FROM tbl t, cte c
UNION ALL
SELECT t.range * c.r                                           , c.val FROM tbl t, cte c
UNION ALL
SELECT t.range * tsrange(upper(c.r), NULL, '[)') - c.r         , t.val FROM tbl t, cte c;

Returns:

range                                           val
---------------------------------------------------
["2010-01-01 14:00:00","2010-01-01 14:10:00")   20
["2010-01-01 14:10:00","2010-01-01 14:30:00")   15
["2010-01-01 14:30:00","2010-01-01 15:00:00"]   20

This applies the operation to all rows in table tbl.
And it works for all ranges (overlapping completely, in parts or not at all).

Note that my 2nd row matches the upper bound of the input value (unlike your example, which is probably unintended).

All bounds are preserved. Input values trump existing values. This is achieved with (example 1st part):

tsrange(NULL, lower(c.r), '(]') - c.r AS range

The lower bound is unbounded and automatically excluded (()
Initially use including upper bound (]); intersect that with the input range, to get the complementary bound to the lower bound of the input range.

To eliminate rows with an empty time range:

WITH cte(r, val) AS (SELECT '[2010-01-01 14:10, 2010-01-01 15:30)'::tsrange, 15)
SELECT * FROM (
<same as above>
   ) sub
WHERE range <> 'empty';

SQL Fiddle.


Speed Up Execution Time of Select Query My question may sound childish, but I am really googling a lot regarding this issue. I have a table tblUsers where 20 columns are present (FirstName, LastName, EmailAddress....), which has around 91,000 data.

When I use Select Query

Select * from tblUsers

It takes around 2 min 27 seconds to complete execution.

Is It really any way to speed up the execution time

Any type of suggestions will be welcomed. Thanks in advance

Is It really any way to speed up the execution time

You are running Select * from tblUsers and expecting it to be fast? You are selecting all columns and all rows from a table, so it will take its own time to return the result set. If there is a clustered index, an index scan would be used, and if the table is a heap, a table scan would be used.

If you want to increase the speed, you either have to add a WHERE clause to make query sargable or limit the number of columns, although the latter will not affect the execution time so much.

Also, if your data file lies on a fast storage (SSDs, Flash Drives, as compared to SATA disks), the query is likely to become faster.

Moral: If you are running select * on a table, don't expect the query to be fast, it would take its own time to retrieve all rows. If you are on a production system, running select * would really be a performance killer, so avoid it.


ENABLE KEYS on a large table is crashing mysql I am trying to copy our data over to a new server, and at the same time, testing our backup-and-restore processes. I have a 2.6GB file created by SELECT INTO OUTFILE, containing 19 million rows. I've been trying to use LOAD DATA INFILE to get it into our new MySQL 5.5 server (this came from a 5.0 server). The table is MyISAM. I get the following after about 35 minutes:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Looking at the error log, we have:

120509  9:47:34 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=67108864
read_buffer_size=16777216
max_used_connections=3
max_threads=151
thread_count=3
connection_count=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 820301 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x906de60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
120509 09:47:36 mysqld_safe Number of processes running now: 0
120509 09:47:36 mysqld_safe mysqld restarted
...
Version: '5.5.19-log'  socket: '/data/databases/mysql/mysql.sock'  port: 3306
Distributed by The IUS Community Project

Note that I've had similar problems using mysql database < database.sql, but I don't know if it's the exact same error message.

Fake edit: So I've discovered that it can load the data just fine, if I disable keys beforehand. It dies when enabling keys. So now I know it's not necessarily the size of the data that's causing problems. This test server is running i686, 32bit, version of MySQL, whereas the table came from a 64bit version of MySQL. Could that possibly contribute to this?

When I asked Question 4: (During the ENABLE KEYS phase, do you see Repair by keycache in SHOW PROCESSLIST?) this is what happens when there is no more room for sorting. You most likely ran out of room if mysqld attempted to switch to Repair by keycache.

If you are stuck with this 2GB Server for testing, try one or more of the following:

Lower bulk_insert_buffer_size to 64M
mysqldump the original data using --skip-extended-insert and reload with it. It will produce one INSERT per row. It will be slower to reload (about 2-3 timer longer) but at least memory will be spared collateral damage
Abandon doing DISABLE KEYS and ENABLE KEYS


Log shipping - switchover and failback We are doing a planned failover and switch back from the primary to DR with mirroring. We do not have a separate monitor instance.

Please help me with the following
1. Is it possible to switch back without the testing changes made in DR. OR fallback will have to include the changes tested from DR?
2. Does taking a backup prior to DR test impact log backup/copy/restore process

Many thanks for your helpful responses.

I would start this test FIRST by taking a copy_only backup, see here for details, of DB1 on server 1.

BACKUP DATABASE [DB1] TO DISK = N'<backup location>' WITH COPY_ONLY

This will let you get back to ground zero at the end or if something get messed up. Then you can turn off mirroring using the command

ALTER DATABASE DB1 SET PARTNER OFF;

After mirroring is turned off I would backup the tail of the log, see here for details, using the command

BACKUP LOG DB1 TO DISK = N'<backup location>' WITH NORECOVERY;

and then restore that backup to DB1 with recovery. That will bring the DR site online. At that point you can then setup mirroring from server 3 to server 4.

*Note: Script this out as much as possible. You do not want to be trying to do this manually if this was a real DR situation.

Also, if you have log shipping on server 1 and it is mirroring to server 2 you should setup all the log shipping jobs on server 2 so if you have a failover you can quickly enable the jobs and start log shipping from server 2 to server 3.

At the end of your testing you can restore the copy_only backup to server 1 reset up mirroring to server 2 and log shipping to server 3.

Hope that helps.


CONTAINS function in MS SQL 2008 R2 I try the following SQL query :

SELECT FirstName, SecondName FROM Persons WHERE CONTAINS(FirstName, 'Mat')

and I got this error :

  Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Person'
  because it is not full-text indexed.

Does I need to index the FirstName column ? What's wrong ?

If you want to use the CONTAINS or FREETEXT you need to create a FULL TEXT CATALOG and create a FULL TEXT INDEX on the column. However, you can use the LIKE statement to validate if a column "contains" a specific string.

SELECT FirstName, SecondName FROM Persons WHERE FirstName LIKE '%Mat%'

You must be aware that the LIKE is slower than the contains but need less configuration.
If you want to use the FULL TEXT CATALOG, you can use this link.

Hope this help you.