petervandivier

RESOLVED Create QRY_PROFILE_LIST_MUTEX blocking with TF 7412

This repro requires 2 (or 3) sessions. in session "A" (create-table.sql), we will...

1. create a table with a CI & filtered NCIs
2. populate some sample data into the table
3. drop/recreate the CI offline

In session "B" (polling-to-tbl.sql), we will...

petervandivier

use FunWithPartitions
go
-- select * from LR_PartitionSummary;

create or alter view LR_PartitionSummary
as
-- http://sqlity.net/en/2483/partition-boundaries/
    select
        file_group_name = f.[name],
        table_schema = schema_name(t.[schema_id]),

petervandivier

create table dude (
    id int identity not null primary key,
    name sysname unique
);
go
create table car (
    id int identity not null primary key,
    name sysname,
    dude_id int not null foreign key references dbo.dude(id)
);

petervandivier

use [master]
go
create database FileDrop;
go
use FileDrop
go
alter authorization on database::FileDrop to sa;
go
alter database FileDrop
    add filegroup foo;

petervandivier

$foo = @{
    bar = 1
    blah = 2
}

function baz {
    [cmdletbinding()]param(
        $foo
       ,$which
    )

petervandivier

select *
from (
    select 1 i, cast(null as int) j union all 
    select null, 1
) x

select sum(i) i_sum, sum(j) j_sum
from (
    select 1 i, cast(null as int) j union all 
    select null, 1

petervandivier

# https://stackoverflow.com/a/16735220/4709762

$conn=new-object data.sqlclient.sqlconnection "Server=localhost;Initial Catalog=foo;Integrated Security=True"
$sql=@"
insert Deadlocks (fname, dl)
select @filename, @text;
"@

$conn.open()

petervandivier

select pool_id
   ,[name]
   ,min_memory_percent
   ,max_memory_percent
   ,max_memory_gb = max_memory_kb / power(1024,2)
   ,max_memory_mb = max_memory_kb / 1024
   ,used_memory_mb = used_memory_kb / 1024
   ,target_memory_mb = target_memory_kb / 1024

petervandivier

I WOULD LIKE to copy arbitrary $data from A to B BECAUSE OF REASONS

AS A DBA I WOULD LIKE to burninate the SSIS packages that currently handle A-B copy BECAUSE OF REASONS

[Copy-DbaTableData][1] is great... BUT I want to execute this as an agent job AND I don't want to require dbatools is loaded into the service account PS profile.

petervandivier

use [master]
go
drop database if exists elephants;
create database elephants;
alter authorization on database::elephants to sa;
alter database elephants add filegroup dumbo;
alter database elephants add file (name=dumbo, filename='c:\temp\dumbo.ndf') to filegroup dumbo;
go
use elephants
go