Find current running queries which takes more CPU How to get the details about the current running queries which are utilizing more CPU.

there is several methods to find the queries that cause high CPU usage, most common waies:

Method 1: use build in SQL server report to find queries with high CPU time.

Method 2: use the following queries:

Query 1: get all statement with CPU usage and from high CPU usage to low

SELECT
       -- using statement_start_offset and
       -- statement_end_offset we get the query text
       -- from inside the entire batch
       SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
                           ((CASE qs.statement_end_offset
                                        WHEN -1 THEN DATALENGTH(qt.TEXT)
                                        ELSE qs.statement_end_offset
                           END
                           - qs.statement_start_offset)/2)+1)
                           as [Text],
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
-- converting microseconds to seconds
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
       -- Retrieve the query text
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
       -- Retrieve the query plan
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time

Query 2:  Top 3 CPU-sapping queries for which plans exist in the cache

SELECT TOP 3
total_worker_time ,
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;

Query 3: get number of core usage as well as CPU time (note this query has a lot of other uses like desk I/O)

SELECT TOP 50
    [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
    [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
    [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
    [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
    qs.execution_count,
    [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
    [Total I/O] = total_logical_reads + total_logical_writes,
    Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
        (
            (
                CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(qt.[text])
                    ELSE qs.statement_end_offset
                END - qs.statement_start_offset
            ) / 2
        ) + 1
    ),
    Batch = qt.[text],
    [DB] = DB_NAME(qt.[dbid]),
    qs.last_execution_time,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5    --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC

EDIT
the following sorted proc is helpful too

sp_who
sp_who2
sp_who3


Querying a view based on a table on which I don't have SELECT permission Suppose I have the following table (in schema 'SchemaX') :

CREATE TABLE [SchemaX].[myTable]...

And the following view, build upon the previous table but on a different schema ('SchemaY) :

CREATE VIEW [SchemaY].[myView] AS
SELECT * FROM [SchemaX].[myTable]
WHERE ...

I give permission to a certain user to read the view :

GRANT SELECT ON OBJECT::[SchemaY].[myView] TO [myUser]

But he is still not able to read it and gets the following error :

  The SELECT permission was denied on the object 'myView', schema 'SchemaY'

If I put the view in the same schema as the table, the query will work.

Can you explain why it is working when both are on the same schema and why it is not when they are on separate schemas?

EDIT: Turns out my schemas had different owners.

Found this article that explains why it doesn't work when schemas owners are not the same :
https://www.itprotoday.com/sql-server/security-through-ownership-chains

Perhaps the schemas have different owners?
Take a look at this: SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners?


De-Duplicate from LISTAGG I am using 11.2 version of oracle.
I want to de-duplicate from LISTAGG.

below is existing data in oracle db:

Speaker | Qualification | Product
P           A              P1
P           B              P2
P           C              P3
P           D              P1
P           E              P2
Q           A              P1
Q           B              P2
Q           C              P1

Want below data:

Spkeaker | Product
P           ;P1;P2;P3;
Q           ;P1;P2;

Can any one help please.

select
  speaker,
  listagg(product, ';')
    within group (order by product)
    as products
from
  (
    select distinct speaker, product
    from existing_data
  ) t
group by speaker
order by speaker ;

Test at: dbfiddle.uk


T-SQL - What's the most efficient way to loop through a table until a condition is met In got a programming task in the area of T-SQL.

Task:

People want to get inside an elevator every person has a certain
weight.
The order of the people waiting in line is determined by
the column turn.
The elevator has a max capacity of <= 1000 lbs.
Return the last person's name that is able to enter the elevator before it gets too heavy!
Return type should be table

Question:
What is the most efficient way to solve this problem? If looping is correct is there any room for improvement?

I used a loop and # temp tables, here my solution:

set rowcount 0
-- THE SOURCE TABLE "LINE" HAS THE SAME SCHEMA AS #RESULT AND #TEMP
use Northwind
go

declare @sum int
declare @curr int
set @sum = 0
declare @id int

IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
    DROP TABLE #temp

IF OBJECT_ID('tempdb..#result','u') IS NOT NULL
    DROP TABLE #result

create table #result(
    id int not null,
    [name] varchar(255) not null,
    weight int not null,
    turn int not null
)

create table #temp(
    id int not null,
    [name] varchar(255) not null,
    weight int not null,
    turn int not null
)

INSERT into #temp SELECT * FROM line order by turn

 WHILE EXISTS (SELECT 1 FROM #temp)
  BEGIN
   -- Get the top record
   SELECT TOP 1 @curr =  r.weight  FROM  #temp r order by turn
   SELECT TOP 1 @id =  r.id  FROM  #temp r order by turn

    --print @curr
    print @sum

    IF(@sum + @curr <= 1000)
    BEGIN
    print 'entering........ again'
    --print @curr
      set @sum = @sum + @curr
      --print @sum
      INSERT INTO #result SELECT * FROM  #temp where [id] = @id  --id, [name], turn
      DELETE FROM #temp WHERE id = @id
    END
     ELSE
    BEGIN
    print 'breaaaking.-----'
      BREAK
    END
  END

   SELECT TOP 1 [name] FROM #result r order by r.turn desc

Here the Create script for the table I used Northwind for testing:

USE [Northwind]
GO

/****** Object:  Table [dbo].[line]    Script Date: 28.05.2018 21:56:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[line](
    [id] [int] NOT NULL,
    [name] [varchar](255) NOT NULL,
    [weight] [int] NOT NULL,
    [turn] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
    [turn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[line]  WITH CHECK ADD CHECK  (([weight]>(0)))
GO

INSERT INTO [dbo].[line]
    ([id], [name], [weight], [turn])
VALUES
    (5, 'gary', 800, 1),
    (3, 'jo', 350, 2),
    (6, 'thomas', 400, 3),
    (2, 'will', 200, 4),
    (4, 'mark', 175, 5),
    (1, 'james', 100, 6)
;

You should try to avoid loops generally. They are normally less efficient than set based solutions as well as less readable.

The below should be pretty efficient.

Even more so if the name and weight columns could be INCLUDE-d in the index to avoid the key lookups.

It can scan the unique index in order of turn and calculate the running total of the Weight column - then use LEAD with the same ordering criteria to see what the running total in the next row will be.

As soon as it finds the first row where this exceeds 1000 or is NULL (indicating there is no next row) then it can stop the scan.

WITH T1
     AS (SELECT *,
                SUM(Weight) OVER (ORDER BY turn ROWS UNBOUNDED PRECEDING) AS cume_weight
         FROM   [dbo].[line]),
     T2
     AS (SELECT LEAD(cume_weight) OVER (ORDER BY turn) AS next_cume_weight,
                *
         FROM   T1)
SELECT TOP 1 name
FROM   T2
WHERE  next_cume_weight > 1000
        OR next_cume_weight IS NULL
ORDER  BY turn

Execution Plan

In practice it seems to read a few rows ahead of where is strictly necessary - it looks like each window spool/stream aggregate pair causes two additional rows to be read.

For the sample data in the question ideally it would only need to read two rows from the index scan but in reality it reads 6 but this is not a significant efficiency issue and it does not degrade as more rows are added to the table (as in this demo)

For those interested in this issue an image with the rows output by each operator (as shown by the query_trace_column_values extended event) is below, the rows are output in row_id order (starting at 47 for the first row read by the index scan and finishing at 113 for the TOP)

Click the image below to make it larger or alternatively see the animated version to make the flow easier to follow.

Pausing the animation at the point where the Right hand stream aggregate has emitted its first row (for gary - turn = 1).
It seems apparent that it was waiting to receive its first row with a different WindowCount (for Jo - turn = 2).
And the window spool doesn't release the first "Jo" row until it has read the next row with a different turn (for thomas - turn = 3)

So the window spool and stream aggregate both cause an additional row to be read and there are four of these in the plan - hence 4 additional rows.

An explanation of the columns shown in the above follows (based on info here)

NodeName: Index Scan, NodeId: 15, ColumnName: id base table column covered by index
NodeName: Index Scan, NodeId: 15, ColumnName: turn base table column covered by index
NodeName: Clustered Index Seek, NodeId: 17, ColumnName: weight base table column retrieved from lookup
NodeName: Clustered Index Seek, NodeId: 17, ColumnName: name base table column retrieved from lookup
NodeName: Segment, NodeId: 13, ColumnName: Segment1010 Returns 1 at start of new group or null otherwise. As no Partition By in the SUM only the first row gets 1
NodeName: Sequence Project, NodeId: 12, ColumnName: RowNumber1009 row_number() within group indicated by Segment1010 flag. As all rows are in the same group this is ascending integers from 1 to  6. Would be used for filtering right frame rows in cases like rows between 5 preceding and 2 following. (or as for LEAD later)
NodeName: Segment, NodeId: 11, ColumnName: Segment1011 Returns 1 at start of new group or null otherwise. As no Partition By in the SUM only the first row gets 1 (Same as Segment1010)
NodeName: Window Spool, NodeId: 10, ColumnName: WindowCount1012 Attribute that groups together rows belonging to a window frame. This window spool is using the "fast track" case for UNBOUNDED PRECEDING. Where it emits two rows per source row. One with the cumulative values and one with the detail values. Though there is no visible difference in the rows exposed by query_trace_column_values I assume that cumulative columns are there in reality.
NodeName: Stream Aggregate, NodeId: 9, ColumnName: Expr1004 Count(*)  grouped by WindowCount1012 according to plan but actually a running count
NodeName: Stream Aggregate, NodeId: 9, ColumnName: Expr1005 SUM(weight)  grouped by WindowCount1012 according to plan but actually the running sum of weight (i.e. cume_weight)
NodeName: Segment, NodeId: 7, ColumnName: Expr1002 CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END - Don't see how COUNT(*) can be 0 so will always be running sum (cume_weight)
NodeName: Segment, NodeId: 7, ColumnName: Segment1013 No partition by on the LEAD so first row gets 1. All remaining get null
NodeName: Sequence Project, NodeId: 6, ColumnName: RowNumber1006 row_number() within group indicated by Segment1013 flag. As all rows are in the same group this is ascending integers from 1 to  4
NodeName: Segment, NodeId: 4, ColumnName: BottomRowNumber1008 RowNumber1006 + 1 as the LEAD requires the single next row
NodeName: Segment, NodeId: 4, ColumnName: TopRowNumber1007 RowNumber1006 + 1 as the LEAD requires the single next row
NodeName: Segment, NodeId: 4, ColumnName: Segment1014 No partition by on the LEAD so first row gets 1. All remaining get null
NodeName: Window Spool, NodeId: 3, ColumnName: WindowCount1015 Attribute that groups together rows belonging to a window frame using the previous row numbers. The window frame for LEAD has max 2 rows (the current one and the next one)
NodeName: Stream Aggregate, NodeId: 2, ColumnName: Expr1003 LAST_VALUE([Expr1002]) for the LEAD(cume_weight)


Scripting execute permissions on stored procedure My goal is to write a script which:

Creates a stored procedure
Grants execution permissions to a particular user on that new stored procedure

On this SO question the recommendation is to add GRANT EXECUTE ON at the end of a script that creates a stored procedure.

However, a permissions issue not letting my own non-admin account to grant permissions on stored procedures trying to execute the stored procedure lead me to believe that that GRANT EXECUTE ON statement is executed each time that the stored procedure above it is executed. Am I incorrect?

Edit with clarification:
In the script involved, the stored procedure statements included BEGIN...END, and was followed by GO, before the GRANT EXECUTE statement.

Take a user created Stored Procedure, MySP and as part of the SQL Script to create the SP add a SQL Statement to Grant execute permissions on the SP.

CREATE Procedure MySP
AS
BEGIN
  SELECT 'HELLO';
END
GO
GRANT EXECUTE ON MySP To SomeUser;
GO

When the SQL is run the SP is created and stored in SQL Server. Once the SP is created the Grant execute statement is executed.

When the SP is executed only the contents of the SP are run.

The Grant execute statement is NOT stored with the SP in SQL Sever. This can be verified in a number of ways

By opening SQL Enterprise Manager, locating the database in which the SP resides and viewing the SP under Programmability.
Use sp_helptext 'MySP' which will return SQL Servers stored version of the SP


Why do MS SQL Server SEQUENCEs not have an ORDER parameter like Oracle? In the documentation of CREATE SEQUENCE for T-SQL, you can see that the CREATE SEQUENCE command has no ORDER parameter.

For comparison, the Oracle docs for CREATE SEQUENCE show the ORDER/NOORDER options:

  ORDER

  Specify ORDER to guarantee that sequence numbers are
  generated in order of request. This clause is useful if you are using
  the sequence numbers as timestamps. Guaranteeing order is usually not
  important for sequences used to generate primary keys.

  ORDER is necessary only to guarantee ordered generation if you are
  using Oracle Database with Real Application Clusters. If you are using
  exclusive mode, sequence numbers are always generated in order.

  NOORDER

  Specify NOORDER if you do not want to guarantee sequence
  numbers are generated in order of request. This is the default.

Does Microsoft SQL Server provide a strong ordering constraint for SEQUENCEs? Or does Microsoft not consider it important in general?

In SQL Server all the sequence numbers are generated on a single instance so they are already inherently ordered. And if you’re just using them as unique numbers it shouldn’t really matter anyway.

The Oracle implementation is different because in a RAC environment (not all Oracle implementations), two sequence numbers could be generated by two different “instances” (that’s the wrong term for Oracle but just drawing equivalency in SQL Server). Normally you wouldn’t care if the one requested first was slower and got a higher number than the one requested later - unless, as the Oracle docs say, you were using the sequences as timestamps to truly reflect the order of the original request.


Exclude database form AG Backup Is it possible to exclude a database from an availability group backup?

My backup jobs looks like this:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AVAILABILITY_GROUP_DATABASES'
@URL= xxx,
@Credential = xxx,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

As seen on ola's Site, you can exclude backups by adding , -database.

An example of this:

  USER_DATABASES, -Db1  All user databases, except Db1

Or in you case, with ag databases:

  AVAILABILITY_GROUP_DATABASES, -Db1 All availability group databases, except Db1

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AVAILABILITY_GROUP_DATABASES, -Db1'
@URL= xxx,
@Credential = xxx,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'


Which Query of JOIN with DISTINCT & JOIN with Subquery has better Performance? In the following two queries: http://sqlfiddle.com/#!9/081f9e/11

#1ST QUERY
SELECT a.id,
       g.names as gamma,
       o.names as omega
FROM alpha a
     JOIN (SELECT ao.alpha_id,
               GROUP_CONCAT(o.name) AS names
           FROM alpha_omega ao
               JOIN omega o on o.id = ao.omega_id
           GROUP BY ao.alpha_id ) o ON o.alpha_id = a.id
     JOIN (SELECT ag.alpha_id,
               GROUP_CONCAT(g.name) AS names
           FROM alpha_gamma ag
               JOIN gamma g on g.id = ag.gamma_id
           GROUP BY ag.alpha_id ) g ON g.alpha_id = a.id
WHERE a.id = 1;

#2ND QUERY
SELECT a.id,
       GROUP_CONCAT(DISTINCT g.name) AS gamma,
       GROUP_CONCAT(DISTINCT o.name) AS omega
FROM alpha a
     JOIN alpha_omega ao ON ao.alpha_id = a.id
     JOIN omega o ON o.id = ao.omega_id

     JOIN alpha_gamma ag ON ag.alpha_id = a.id
     JOIN gamma g ON g.id = ag.gamma_id
WHERE a.id = 1
GROUP BY a.id;

Which one of them is better to be used to achieve the optimal performance and efficiency?

The First one uses JOIN with SubQueries while the Second one uses JOIN with DISTINCT, In the First one I get exactly 8 values out from gamma & omega columns,

But in the Second one, I get duplicated values up to the double, Which makes me get 16 value, Then I use DISTINCT to make it 8 values

Keeping in mind that I've set the correct INDEXes to achieve the maximum speed from INDEX.

Which is better to be used on the long-term exactly?

Let's say I have 50,000 record in each Table, And the Query is executed 20,000 time per Sec.

The first one is good, when you select a larger range, the engine should no longer treat the "subqueries" as such, but run them independantly, and join the results.


Blocking upsert procedure Setup

I have a simple table:

CREATE TABLE [dbo].[StringData](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Hour]  AS (dateadd(hour,datediff(hour,(0),[TimeStamp]),(0))) PERSISTED,
    [Date]  AS (CONVERT([date],[TimeStamp],(0))) PERSISTED,
    [DCVoltage] [decimal](18, 2) NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    [DCPower]  AS (([DCVoltage]*[DCCurrent])/(1000)) PERSISTED,
    [IsCompressed] [bit] NULL,
    [TimeStamp15Minutes]  AS (dateadd(minute,(datediff(minute,(0),[TimeStamp])/(15))*(15),(0))),
CONSTRAINT [PK_StringData1] PRIMARY KEY CLUSTERED
(
    [TimeStamp] DESC,
    [DCStringID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

And I have an upsert procedure called [InsertOrUpdateStringData]

ALTER PROCEDURE [dbo].[InsertOrUpdateStringData]
@DCStringID bigint, @TimeStamp datetime, @DCVoltage decimal(18,2), @DCCurrent decimal(18,2)
AS
/****** avoid parameter spoofing ******/
DECLARE @DCStringID_Value AS bigint; SET @DCStringID_Value = @DCStringID;
DECLARE @TimeStamp_Value AS datetime; SET @TimeStamp_Value = @TimeStamp
DECLARE @DCVoltage_Value AS decimal(18,2); SET @DCVoltage_Value = @DCVoltage
DECLARE @DCCurrent_Value AS decimal(18,2); SET @DCCurrent_Value = @DCCurrent
/************/

MERGE [StringData] AS TARGET
USING (VALUES (@DCStringID_Value, @TimeStamp_Value))
    AS SOURCE ([DCStringID], [TimeStamp])
    ON TARGET.[DCStringID] = SOURCE.[DCStringID] AND TARGET.[TimeStamp] = SOURCE.[TimeStamp]
WHEN MATCHED THEN
    UPDATE
    SET [DCVoltage] = @DCVoltage_Value,
    [DCCurrent] = @DCCurrent_Value
WHEN NOT MATCHED THEN
    INSERT ([DCStringID], [TimeStamp], [DCVoltage], [DCCurrent])
    VALUES (@DCStringID_Value, @TimeStamp_Value, @DCVoltage_Value, @DCCurrent_Value);

Using

Many different application threads are using the [InsertOrUpdateStringData] procedure to upsert data to the table synchronous.

The table indexes are use well and one single excecution is quite fast and take around 31ms.

Problem

If another non-select table operation is excecuted (like insert bulk) the are block for a very long time.

Unfortunately [sp_WhoIsActive] is not showing me if the [InsertOrUpdateStringData] procedure calls are also blocking each other.
But it looks like there is a lock chaining because its the only explanation for locking over 10,000ms like in my example.

Question

It seems that my upsert procedure is locking the complete table and slow down other insert operations.

Is the anything I can do to optimize my queries to avoid longtime blocking?

Update 1 - related to Nic answere

The lock happens to all non-select operations, as I wrote bulk insert is only a sample.

Please see here a 10sec lock simple merge call (blocking session 176 is a [InsertOrUpdateStringData] call):

I also tried @get_locks=1 here is the result. Maybe it helps you with more details.

Update 2 - related to Nic answere

Here is one example:

sid 52 is blocked by sid 119 for over 40sec!? But sid 119 seems not to be blocked. I really not understand this.

Since your spid 119 is in [sleeping] state, I'd recommend you to check:

DBCC Opentran() to check whether there is any open transaction on 119 (high possibility to my experience)
sp_lock 52 to check what lock in spid 52 is NOT granted
sp_lock 119 to check what lock it holds, and you can trace the lock back to the resource (such as table or index or key etc)

Also maybe you can turn on READ_COMMITTED_SNAPSHOT on your database to avoid the blocking between read and write operation.


what is hitting your db? What is a quick way of finding out what is hitting your database especially when the log files are going crazy. SQL profiler? If so, how?

The quickest (and cheapest) way to find out what is hitting your databases is to use sp_whoIsActive (http://whoisactive.com/)


Database suitable for data mining What would be an appropriate / suitable database sans Oracle that would be suitable for the following conditions?

data mining
 includes but does not limit to the following:
 a. clicking on links
 b. capture geolocation data, platform details
 c. user behaviours
there will be a foreseen exponential surge of data being captured as more apps / sites being deployed
for now, there are on average 60,000 ~ 80,000 data being captured every 15 minutes for 2 main sites / apps
In the near future, we are also planning to release all these data to the various parties involved where they can use it for their own apps / sites.

As of now, we are using mySQL but we found that it becomes very costly as we started to scale up. As such, any advice would be good as a starting point.

Mahout with HBase is probably a good option for very large datasets.

It is designed to run on many cheap commodity servers.

Also, monetDB is an open-source column-wise database which is very fast for data warehouses.


alter primary key and all references in sql server I'm trying to alter a primary key field but is being referenced by about 93 tables.

I used the following script to drop and create the FK constraints: https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx

but I still need to alter all those referenced fields and they seem to reference others.

Anyone know an script that can handle all of that or knows a tool that can help me achieve this.

Thanks in advance

The SSMS table designer will generate this script for you.  Just make the change in the designer and generate the change script.

There's a a safety config you have to set before the designer will generate scripts that drop and recreate tables.  That should be done on production servers only after testing.


How to optimize and estimate DB recovery times? Would like to optimize recovery time for db's after failover to passive node in high availability cluster.
Assuming full recovery model...
What are the steps to take to optimize DB recovery time for a given SQL server database?
Is there a good way to estimate how long recovery time will take for a given SQL server database?

What are the steps to take to optimize DB recovery time for a given SQL server database?

The best way is to upgrade to SQL Server 2019 and use Accelerated Database Recovery which provides:

Fast and consistent database recovery

Instantaneous transaction rollback

Aggressive log truncation

On older versions you can configure the recovery interval to force more aggressive checkpointing of dirty pages, but in-flight transactions will still need to be rolled back during a failover.  So you would also want to avoid long-running transactions during windows where you need fast failover.
Recovery time during large transactions might be optimized by temporarily switching the database to the Bulk Logged recovery model, but you'd need to ensure that the operations qualify for minimal logging.


Do SQL Server stored procedures cache data results? I have heard this from friends, but I never investigated whether this is true.

Is it true that the data results of an executed query are stored in cache?

I mean, if I have a stored procedure like :

SELECT * FROM USERLIST

...is it true that the result (list of users, in this case) is stored in cache.

Also, if I have these :

SELECT * FROM USERLIST WHERE user="user"
SELECT * FROM USERLIST WHERE user="userzzz"

(in which user/userzzz are passed as parameters), is it correct that it stores 2 different results on the database cache.

I don't think so, but I want the confirmation from you, experts!

Query results are not cached

However, the source table and index data and metadata will be cached after the 1st use (subject to continued use, load and memory pressure though)

That is, the results of a query will be evaluated every execution but the tables(s) (and any indexes etc) used by the query will most likely be in memory already.

The compiled execution plan will be cached which is where the confusion comes from I suspect


Add columns Query With parameters Hello all I'm having 1 Question with following query syntax

 ALTER TABLE table_name
 ADD column_name column-definition;

Question is can we use parameters to column_name field like @column_name? Because I'm creating columns which Column name is supplied by front end as a parameter. for example

ALTER TABLE table_name
ADD @column_name varchar(250);

this is giving error so any alternate for this?

Perhaps try:

DECLARE @sql varchar(max);
SET @sql = 'ALTER TABLE table_name ADD ' + @column_name + ' varchar(250);';
EXEC (@sql);


MySQL Enterprise Backup download site I was given a sever to use for tests that has MySQL commercial version installed. I would like to test backups using MySQL MEB, but cannot find the download site. I have seen many blogs mentioning that I need a file

meb-3.12.3-linux-glibc2.5-x86-64bit.tar.gz

But cannot find a website where I can actually download the MEB file. Should MySQL Backup already come pre-packaged with the MySQL commercial version and just need to find the executable file? I am new to MySQL enterprise, but familiar with mysqldump and xtrabackup. Unfortunately, mysqldump is too slow, and xtrabackup does not work with MySQL Enterprise.

It is unclear if the commercial version you mention is a trial or a purchased version of MySQL Enterprise. You should get Oracle credentials for download in the first case associated with your license.

In both cases, you have here a link to both the trial and licensed versions of MySQL Enterprise Edition (including Enterprise Backup):

https://www.mysql.com/trials/


Amazon RDS I/O requests - 1 query = 1 billable I/O? I have an InnoDB database that I'd like to migrate onto Amazon RDS.

My current implementation, hosted on my own server, shows roughly 8M queries per month.

The RDS site say that
I/O Rate    $0.10 per 1 million requests

Does 1 I/O = 1 query?  ie, will I be billed $80/mo for this amount of usage + the RDS fees?

WARNING : Be very careful with your numbers and what you view as a Query !!!
Why am I giving such a warning ???
Back in August 2011, I wrote post in ServerFault explaining how it is possible for 1 Billion Queries to be executed in 24 days.
Here is that entire post :
MySQL will call for queries internally. In fact, just about anything you do in MySQL is a query.
If you turn on the general log or the slow query log, everything mysqld does gets recorded.
If you have --log-queries-not-using-indexes enabled, everything not involving indexes lands in the slow log.
Let's say you run this query:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| annarbor           |
| dude               |
| example            |
| garbage            |
| lovesh             |
| mysql              |
| performance_schema |
| replagdb           |
| stuff              |
| test               |
| tostinni           |
| wordpress          |
| zipcodes           |
+--------------------+
14 rows in set (0.06 sec)

Yes, SHOW DATABASES; is a query. In fact, what the information_schema equivalent ???
mysql> select schema_name "Database" from information_schema.schemata;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| annarbor           |
| dude               |
| example            |
| garbage            |
| lovesh             |
| mysql              |
| performance_schema |
| replagdb           |
| stuff              |
| test               |
| tostinni           |
| wordpress          |
| zipcodes           |
+--------------------+
14 rows in set (0.08 sec)

Does the table information_schema.schemata have an index ???
mysql> show create table information_schema.schemata\G
*************************** 1. row ***************************
       Table: SCHEMATA
Create Table: CREATE TEMPORARY TABLE `SCHEMATA` (
  `CATALOG_NAME` varchar(512) NOT NULL DEFAULT '',
  `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '',
  `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
  `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',
  `SQL_PATH` varchar(512) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

No, it does not. So, SHOW DATABASES; would land in a general log and the slow log (with --log-queries-not-using-indexes enabled)
Therefore, many operations we do not think would constitute a query may just be a query, but internal to mysqld.
If you are using any monitoring tools that are connected to mysqld, this would also run up the counts on queries.
Example:
mysql> show global status like 'uptime'; select * from information_schema.global_status where variable_name='uptime';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 613   |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| UPTIME        | 613            |
+---------------+----------------+
1 row in set (0.00 sec)

Just retrieving the uptime of mysqld is a query. Internally, how does MySQL count the queries being executed ? Here are two status variables that may shed some light:

Queries : The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.

Questions : The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Please don't be that concerned if your MySQL Server is being monitored because the monitoring that calls for status variables are running queries internally to retrieve requested data.
1 billion in 24 days is

41.7 million queries per day
1.736 million queries per hour
28,935 queries per minute
482 queries per second

For a MySQL instance that is being monitored, these numbers are not farfetched at all.
If you are using MySQL Workbench, MySQL Administrator, or phpMyAdmin, any page these products generate or update will summon these little status queries and run numbers up quickly.
SUMMARY
If your site does indeed make 8M queries, then an I/O Rate $0.10 per 1 million requests, should be $0.80 (80 cents) a month. If you execute 1 Billion queries in a Month, that $100.00. Please make absolutely sure these numbers jive and GET IT IN WRITING WITH YOUR CFO SITTING NEXT TO YOU !!!
UPDATE 2012-05-02 16:26 EDT
Since it is 800M queries/month, that's $80.00/month


mysql trigger to trigger only if record doesn't exist The following trigger :

DELIMITER //
CREATE TRIGGER auo_mtltp  AFTER UPDATE ON master_table_linfomasltp2016
FOR EACH ROW
BEGIN
            IF (NEW.IMHQ_solicitado = 'SI')
            THEN
            INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad,n_marcadores_solicitados)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad,OLD.n_marcadores_solicitados);
            END IF;

            IF (NEW.FISH_solicitado = 'SI')
            THEN
            INSERT INTO fish (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad);
            END IF;
    END//
DELIMITER ;

Adds a new row into a table if x condition is met. How ever, ID_CASO is a unique index into the child table, and like this, if i update a record already into the child table, it gives me an error, since there is another trigger running that adds the record to the child table if the x condition is met since insertion.

I need this trigger to run only IF the ID_CASO value is not present in the child table.

Any ideas?

I tried IF EXISTS syntax, but no succes so far.

Also, I thought that if you clarified the "NEW." prefix into the row identifier it would only fire the trigger if the value is NEW, but if the value is OLD and there is no NEW value being updated, it uses the OLD one and tries to add the row into the child table. As in, it just runs the trigger again, even if the value was OLD and not NEW.

Change the inserts to

INSERT IGNORE ...

That should avoid the problem as I read it.  If I misread, please clarify.


What Rules Determine When SQL Server Use a CTE as an "Optimization Fence"? A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL:

Two Important Differences Between SQL Server and PostgreSQL

The first point (“CTEs are optimization fences”) caught my eye, because it is obvious that in the example provided, SQL Server combines the CTE and the main query together and optimizes it as a single query (as opposed to the opposite behavior in PostgreSQL).

However, this behavior seems contrary to the examples that I have seen in other blogs and training classes, where SQL Server does treat the CTE as an optimization fence, which allows for better use of indexes, better performance, etc.  For example:

A Better Way To Select Star

So, it seems like SQL Server “honors” the CTE as an optimization fence SOMETIMES.  Are there any good resources available that document the specific list of known cases where SQL Server will reliably honor the CTE as an optimization fence (or the opposite behavior)?

...list of known cases where SQL Server will reliably honor the CTE as an optimization fence

Any such list would rely on observed behaviour, with no guarantee of reliability.

The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.

CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.

Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.

Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:

The optimizer should considering materialising results of CTEs
Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)

In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.


SQL Server Data Tools for VS 2015 - Anyone using for SSIS development to multiple versions? According to this link, https://www.mssqltips.com/sqlservertip/4253/backwards-compatibility-in-sql-server-data-tools-for-integration-services/
 the newest version of SQL Server Data Tools - "SSDT for Visual Studio 15" (16.5) is compatible with SQL 2016 AND older versions as well. In previous incarnations of the software, whether it was called "SQL Server Data Tools", "SQL Server Data Tools - Business Intelligence", or "Business Intelligence Development Studio", we always seemed to run into version incompatibility issues when deploying.

If I'm reading this correctly, we could uninstall all older versions of the tools, and just install SSDT 16.5 for development of SSIS packages for SQL 2012 and newer. Due to many issues I've seen in past versions of the tools, I am hesitant to develop new projects in this.

Is anyone successfully deploying SSIS packages to SQL 2016 AND older versions with SSDT 16.5? Obviously, tasks that are only supported in newer versions wouldn't work in older versions.

Visual studio 2015 SSDT work with SQL Server 2016 and has a backward compatibility to SQL server 2014 and 2012 only.

Target version of SQL Server    |   Development environment for SSIS packages
2016    |   SQL Server Data Tools for Visual Studio 2015
2014    |   SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence for Visual Studio 2013
2012    |   SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence for Visual Studio 2012
2008    |   Business Intelligence Development Studio from SQL Server 2008

The following link contains very helpful informations:

For SQL Server 2008 /R2 editions

Integration Services versions Side by side

You have to install Microsoft Visual Studio 2008 to deploy package for SQL Server 2008 or SQL Server 2008 R2 by default. (newer versions will not work correctly)

There are a lot of links saying that newer versions of visual studio cannot deploy SSIS 2008 packages correctly:

https://stackoverflow.com/questions/10451053/can-sql-server-data-tools-work-with-sql-server-2008-ssis
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bff5e145-1c63-4221-b2ba-009ea96a9e05/can-i-use-vs2012-to-create-and-deploy-ssis-for-sql2008r2?forum=sqlintegrationservices
https://www.sqlservercentral.com/Forums/Topic1560322-364-1.aspx


MySQL Replication - Master Master - Single Master write I am looking to improve our fault tolerance for our persistent store. After reading several online docs about how to set-up master <> master replication, I'd still like to ask the experts about the order of set-up, to ensure I don't miss anything important.

Our setup will be a master <> master, however, only one master will be written to at a time. This will be controlled via "floating ip" which can be set to point at the secondary master, if the primary master fails.

With a single write master <> master setup, I don't need to worry about setting the auto_increment_offset correct?
Does MySQL replicate all databases by default, or do I need to explicitly identify them via binlog-do-db?
Do I need to do an export from master1 and import to master2, before I start replication, or can I start master 2 to pick up all transactions over the last several months?
Just to be clear, I need to setup a "replicator" user (with replication perms) on both master1 and master2?

-- Update 8.15.2014 --

This is the guide I followed for my intial setup

To answer question #2, I did the following:

Notice that I have a performance_schema table, this is because I am using Percona MySQL. Per Rolando's edit, the following is not needed if you want to replicate ALL databases, however I am leaving here for posterity to show others how they can specify multiple databases, if they do not want to export all.

binlog_do_db = information_schema
binlog_do_db = mysql
binlog_do_db = performance_schema
binlog_do_db = example_blog
binlog_do_db = example_core
binlog_do_db = example_log

To answer question #3, I ended up creating a snapshot of Master1 by issuing the following command:

mysqldump -h localhost -p -u root --opt --all-databases --single-transaction --master-data  > /tmp/example_snapshot_`date +%Y_%m_%d__%H_%M_%S`.sql

Notice the --master-data param, according to mysql docs, this allows us to create snapshot without have to create a new session and deal with table locking. However it does add an extra line the resulting .sql file:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=107;

I removed this line b/c I wanted to affect this change manually, on each master.

Happy to report that replication appears to be working. Thanks Rolando!

With a single write master <> master setup, I don't need to worry about setting the auto_increment_offset correct?

Yes, you are right. I have setup Master-Master setups for years with one Write Master. I never touched the auto_increment options. Never had an incident.

  Does MySQL replicate all databases by default, or do I need to explicitly identify them via binlog-do-db?

Yes, it is by default. Although binlog-do-db exists, I would not recommend it since you are doing Master-Master. If you have additional slaves, it is best to setup filtering on the slave. That way, you have all binlog events to point-in-time recovery and other incidentals. Such incidentals might include Star Topologies (which feature Distribution Masters, a Master that does not house any data, only binlogs Such binlogs could be filter with binlog_do_db to reduce binlog traffic to slaves).

  Do I need to do an export from master1 and import to master2, before I start replication ?

You do need to to export from master1 and import to master2.

  can I start master 2 to pick up all transactions over the last several months?

The only way you can do that is if you had every binlog since you loaded master1 aznd never erased any of the binlogs. Most people rotate their binlogs, making this impossible.

I need to setup a "replicator" user (with replication perms) on both master1 and master2?

Yes

EPILOGUE

I have setup ucarp over the years as the failover mechanism for the floating IP. Most use Linux Heartbeat, Pacemaker, and others. So, you are in the ballpark of what needs to be done.


SQL Percentage of users falling in a specific range I have a list of users with corresponding date of birth.
Is there any possibility I can get a percentage of users born between 1990 and 2000 in a single SQL query?

The query I tried is as follows:

Select (Select count(distinct user) as users from Visitors
  where birthyear between 1990 and 2000)/count(distinct user) from Visitors

But it gives me a zero as result.

SELECT 100.0 * SUM(birthyear between 1990 and 2000) /
               COUNT(*)  AS pct
    FROM ( SELECT userid, MAX(birthyear) AS birthyear
             FROM visitors GROUP BY userid ) AS x

Some notes:

SUM(boolean-expr) is a way to count how often it is TRUE.
between 1990 and 2000 includes 101 distinct years because BETWEEN is 'inclusive'.  Are you sure you wanted that?
COUNT(*) will count all the rows from the derived table (the subquery)
MAX is a kludge -- A visitor might put down different birthyears on different visits.
Whenever a subquery is used, it must have parentheses around it.
The 'composite' index INDEX(userid, birthyear) would help performance some.


mysql row returned n times based on column value n Say I have a given table with the following data:

| id | pid | email           | entries |
|----|-----|-----------------|---------|
| 1  | 1   | foo@example.com | 3       |
| 2  | 1   | bar@example.com | 1       |
| 3  | 1   | baz@example.com | 2       |

id is the primary key.

pid,email has a unique index.

entries indicates the number of entries the person has into a prize draw.

How can I query the above data to get the following result:

| id | pid | email           | entries |
|----|-----|-----------------|---------|
| 1  | 1   | foo@example.com | 3       |
| 1  | 1   | foo@example.com | 3       |
| 1  | 1   | foo@example.com | 3       |
| 2  | 1   | bar@example.com | 1       |
| 3  | 1   | baz@example.com | 2       |
| 3  | 1   | baz@example.com | 2       |

where each row is returned n times based on the entries column a row has?

The reason is to hand the result to a randomized name drawer where the more entries you have the more chances you have of winning.

Currently, we have the setup where each entry creates a new row in the table but this starts having performance issues when certain entrants make a thousand entries in one go. This generates thousands of inserts at a time and when we have a popular prize we are running into performance issues where inserts start taking too long as there are so many.

If we could restructure each how we store the data we can then just have 1 upsert query when, for example, someone gets 1000 entries in one go rather than generating 1000 individual inserts.

For version 8+

WITH
cte AS ( SELECT 1 num
         UNION ALL
         SELECT num+1 FROM cte WHERE num < ( SELECT MAX(entries) FROM table)
       )
SELECT table.*
FROM table, cte
WHERE table.entries <= cte.num

For version 5.7 we must generate numbers table in the query

SELECT table.*
FROM table,
     ( SELECT 100*t1.num+10*t2.num+t3.num+1 num
       FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
     ) cte
WHERE table.entries >= cte.num

The number of tX pseudo-tables is dependent by max possible entries value. In the code above this max value assumed to be 1000.

Or you can generate numbers table using user-defined variable based on some (the same or another) table (if such table exists) for which the records count is over max possible entries value.

Or you can create static numbers table in this or system database and use it.


Insert a large TEXT. Is INSERT the only way? I am not a strong expert in databases at this level, so I was wondering the following.

A database I am currently working on has a large TEXT field where potentially hundreds of megabytes are stored in each row. I understand this is not optimal design but it predates me and I can't change it now.

The insertion of the data happens with a single SQL query INSERT INTO, which of course results in a gigantic query. I was wondering the following:

Is INSERT the only option in this case when submitting large blob of data? maybe there's a SQL extension to handle such large transfers that is better optimized.
Having a single query of this size obviously requires that the whole transfer of these hundreds of megabytes goes smooth without any network issue for the whole duration of the transfer. How resilient is this to network issues, and can it be restarted in case of failure without having to reissue the whole query again (and therefore restart the whole transfer from scratch)?

I am using the latest MySQL, but I wonder also about other databases for personal awareness.

I recommend avoiding putting more than 16MB (perhaps less) in a column in MySQL.  There are many limitations in MySQL and possible in the companion tools you are using.

For storing Text (as opposed to blobs) in a column, I would strongly consider compressing it.  This is likely to shrink the data by 3x.  Do the compression in the Client, not the Server.

What is the Text for?  How will be it be used eventually?  An analogy: with images (which must be stored in BLOB/MEDIUMBLOB/LONGBLOB), it can be better to store them as files when the use is HTML's <img src=file...>.  This makes the fetching trivial.

I once had the task of storing large data, even multi-GB movies.  Obviously, I could not even use LONGBLOB (4GB limit).  I chose to break the stream into 50KB (pick a number) chunks and store them in a BLOB in rows of a table.  Together with it was a sequence number to allow reconstructing the result.  This allowed me to "stream" things rather than "store (or fetch) it all at once".  Hence I was not straining the limits of the client software (which was PHP and Apache).

As for inserting blob (or compressed text), it may be optimal to convert it to hex, then use UNHEX("...") in the INSERT statement.  This completely avoids any issue of escaping strings, etc.  (But does not avoid SQL Injection problems.)  It does, however, cut in half some of the limits you may encounter.


Unable to install SQL Server Management Studio (SSMS) on server We are experiencing persistent errors when attempting to install SSMS on one of our development database servers. We have tried the installers for versions 17.4 and 17.9.1, but the error messages are the same.

Extracts from SSMS-Setup-ENU_20190319131949.log (17.4)

10C0:10B8][2019-03-19T13:28:23]i000: MainViewModel.OnPackageActionProgress: Percent completed: 50, Overall progress: 65
[1E58:1E70][2019-03-19T13:28:24]e000: Error 0x80070643: Process returned error: 0x643
[1E58:1E70][2019-03-19T13:28:24]e000: Error 0x80070643: Failed to execute EXE package.
[10C0:10B8][2019-03-19T13:28:24]e000: Error 0x80070643: Failed to configure per-machine EXE package.
[10C0:10B8][2019-03-19T13:28:24]i000: MainViewModel.OnPackageAction: Install Completed for package Microsoft Visual Studio Tools for Applications 2015 Language Support
...
[10C0:10B8][2019-03-19T13:33:28]e000: MainViewModel.OnBundleAction: Bundle action failed: Fatal error during installation (0x80070643)
[10C0:10B8][2019-03-19T13:33:28]i399: Apply complete, result: 0x80070643, restart: None, ba requested restart:  No
[10C0:1C40][2019-03-19T13:33:42]i000: MainViewModel.OpenUrl: Opening url: C:\Users\******~1\AppData\Local\Temp\SsmsSetup\SSMS-Setup-ENU_20190319131949.log

Extracts from SSMS-Setup-ENU_20190319134134.log (17.9.1)

[16C4:2298][2019-03-19T13:49:35]i000: MainViewModel.OnPackageActionProgress: Percent completed: 50, Overall progress: 67
[2324:1D80][2019-03-19T13:49:35]e000: Error 0x80070643: Process returned error: 0x643
[2324:1D80][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to execute EXE package.
[16C4:2298][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to configure per-machine EXE package.
[16C4:2298][2019-03-19T13:49:35]i000: MainViewModel.OnPackageAction: Install Completed for package Microsoft Visual Studio Tools for Applications 2015 Language Support
[16C4:2298][2019-03-19T13:49:35]i319: Applied execute package: VSTALS2015, result: 0x80070643, restart: None
[16C4:2298][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to execute EXE package.
[2324:1D80][2019-03-19T13:49:35]i351: Removing cached package: VS2015KB3095681Update, from path: C:\ProgramData\Package Cache\02A26E554FBB4232ACD36E70D09F2C7893D399CD\
...
[16C4:2298][2019-03-19T13:55:14]e000: MainViewModel.OnBundleAction: Bundle action failed: Fatal error during installation (0x80070643)
[16C4:2298][2019-03-19T13:55:14]i399: Apply complete, result: 0x80070643, restart: None, ba requested restart:  No
[16C4:1798][2019-03-19T13:56:28]i000: MainViewModel.OpenUrl: Opening url: C:\Users\******~1\AppData\Local\Temp\SsmsSetup\SSMS-Setup-ENU_20190319134134.log

The installer takes many minutes to arrive at this point, but fails with the same result each time.

We have restarted the server in between each attempt, and have run on the latest Windows Updates. We have .NET Framework 4.5.2 installed on the server. All attempts have been made using accounts with full admin permissions on the server.

Can anyone please advise how we can get this installed successfully?

As per MSDN BOL documentation here  for the SSMS 17.9.1 installation the .NET Framework should be 4.6.1 (or greater).

Make sure you are running an up-to-date version of the Visual C++ 2013 Redistributable Package. Version 12.0.40649.5 (or greater) is required. Only the x64 version is needed. And also all the latest OS updates are installed on the computer.

For further your ref here


Get all field names from all tables in Sybase DB I've been tasked to get all the fields in all tables in a Sybase DB. Anyone know how to do this? Thanks in advance.


Why my Oracle 12c database seems not to be started? I have installed an Oracle 12c database on my development environment, which I start only once a while for integration testing. But today, it does not seem to start, or did it start but does not allow connections? Here are some clues:

OS is Debian 8
I use a shell script to start and stop the database
$ORACLE_SID contains

  INFO

The hosts file contains

  127.0.0.1 localhost and 127.0.1.1 Strada.nohoo.biz    Strada

The /u01/app/oracle/diag/rdbms/info/INFO/alert_INFO.log file ends with

  Completed: ALTER DATABASE OPEN Sun Mar 11 20:27:57 2018 Shared IO Pool
  defaulting to 112MB. Trying to get it from Buffer Cache for process
  4294.
  =========================================================== Dumping current patch information
  =========================================================== No patches have been applied
  =========================================================== Sun Mar 11 20:27:57 2018 db_recovery_file_dest_size of 4560 MB is 0.00% used.
  This is a user-specified limit on the amount of space that will be
  used by this database for recovery-related files, and does not reflect
  the amount of space available in the underlying filesystem or ASM
  diskgroup.

tnsping INFO returns

  TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on
  11-MAR-2018 21:42:22

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

  Used parameter files:
  /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

  Used TNSNAMES adapter to resolve the alias Attempting to contact
  (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT =
  1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
  INFO.nohoo.biz)))

  OK (0 msec)

tnsnames.ora contains

INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = INFO.nohoo.biz)
    )
  )

listner.ora contains

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

lsnrctl services returns

  LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-MAR-2018
  21:54:24

  Copyright (c) 1991, 2014, Oracle.  All rights reserved.

  Connecting to
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

  The listener supports no services

  The command completed successfully

The ps -A | grep ora command in a shell returns a list of

  6003 ?  00:00:00 ora_pmon_info
   6005 ?  00:00:00 ora_psp0_info
   6007 ?  00:00:00 ora_vktm_info
   6011 ?  00:00:00 ora_gen0_info
   6013 ?  00:00:00 ora_mman_info
   6017 ?  00:00:00 ora_diag_info
   6019 ?  00:00:00 ora_dbrm_info
   6021 ?  00:00:00 ora_vkrm_info
   6023 ?  00:00:00 ora_dia0_info
   6025 ?  00:00:00 ora_dbw0_info
   6027 ?  00:00:00 ora_lgwr_info
   6029 ?  00:00:00 ora_ckpt_info

But all this does not look like a successful situation!

And finally, trying to connect with SQLplus ends up with

  ORA-12547: TNS:lost contact

Thanks for you help!

Your database seems to be up and running, but it was not registered dynamically into the listener.

Try the below:

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=info

sqlplus / as sysdba
SQL> show parameter local_listener

Check the value of local_listener. You have 3 options here:

parameter value is empty - in this case, the database registers itself into the listener running on port 1521
parameter value is something like LISTENER_INFO - this is a TNS entry that resolves to the address of the listener. If tnsping LISTENER_INFO resolves to a valid address, the database should be able to register itself to the listener specified by that
parameter value is something like (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)). No name resolution is required, database registers itself in the listener.

You most likely have 2. with an unresolvable name, or 3. with and invalid network address.

You can try fixing it with:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))';

Which sets the specified address.

Or:

alter system set local_listener='' scope=memory;
alter system reset local_listener;

Which clears the value of local_listener in memory and spfile, so the database will try registering into a listener running on the default 1521 port.

If this still does not help, you can register your database in the listener statically, by adding the below in listener.ora:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = info.nohoo.biz)
   (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   (SID_NAME = info)
  )
 )

After that reload the listener:

lsnrctl reload

Or restart the listener:

lsnrctl stop
lsnrctl start

And try logging in again.


Cannot add new Server Role SQL Server 2014 It seems that I have the same problem as Yasser in this post

I cannot add a new Server Role "SQL Server 2008 R2"

but I am running SQL Server 2014.

Basically when I right click on Server Roles I only get a menu with three options, "Start Power Shell, Reports, Refresh".

The command CREATE SERVER ROLE works. I am logged in as "sa". Any advice would be really helpful.

Thank you.

This is a known bug in Management Studio, and has existed since at least SQL Server 2012. Unlike other missing things in Management Studio (like SQL Server Agent), this has nothing to do with the version or edition of Management Studio you're using, but rather the edition of SQL Server that Object Explorer has connected to. Here's confirmation from one of the lead SSMS devs:

You will need to use T-SQL for this functionality until they fix it (which I think after 2016 SP1 they'll need to do a full audit of any instance-specific filtering or restrictions).


Database IO comparison We're running sql server on vmware. Our san is pretty new but still I think I/O is pretty crappy even though I'm supposed to be on separate spindels. Server speeds are:

My client machine:

Is our san slow? Can I get some comparative values from other users? If so please let me know if you're on a vm or a physical machine. It looks like it's doing pretty ok writing but reading is really terrible...

The performance numbers that someone else gets from their array compared to the numbers that you get are totally meaningless.  I could have 1000 disks in my array backed with 512 Gigs of cache or I could have 2 7200 RPM disks in a RAID 10.

Without knowning the exact configuration that your array is configured in the numbers are just numbers.

Another thing to note is that 4k IO are a worthless measure as are 512k IO.  SQL Server will be doing almost all of it's IO in 64k IO.


How to Grant A User Under A Sql Domain Account To Insert and Update Records I have a sql domain account in sql server like DOMAIN\GroupName. There are many user accounts under that account like DOMAIN\user.name. I want to give insert and update permission only to a user under that domain account. How can i do it for only one user?

Sounds like DOMAIN\GroupName is a group and DOMAIN\user.name is a member of that group. This means that DOMAIN\user.name  has access to SQL Server through that group. You have 2 options:

Create another group, let's say DOMAIN\NewGroupName and grant that group INSERT and UPDATE, then make DOMAIN\user.name a member of that group.
This is not the fastest solution, but it's a clean solution and it will make your life easy long term if you will get more and more users asking for the same rights as DOMAIN\user.name. You will need rights on AD (I assume DOMAIN\NewGroupName is an AD group.)
Just create an individual login for DOMAIN\user.name and grant him access + UPDATE and INSERT to that DB. You find documentation on how to create the login here. After you have created the login, just right click on it and go to properties --> user mapping --> select the desired database --> select db_datawriter --> click ok. You find more about database roles and access here. If you need that user to have access to all DBs on that server, you can look at server roles (listed on the properties page right above user mapping) and you have some nice documentation about that here.


Performance issue with TOP (1) on a nullable column I need help to identify the reason for a performance issue.

Actually following query sits in a scalar-valued-function but that is not the reason for the issue since it needs the same time as query:

SELECT TOP (1) dd.gsp_part_price
FROM  tabdata
       INNER JOIN locsl
               ON tabdata.fisl = locsl.idsl
       INNER JOIN locgsp
               ON locsl.figsp = locgsp.idgsp
       INNER JOIN tabdatadetail dd
               ON dd.fidata = tabdata.iddata
       INNER JOIN tabsparepart sp
               ON dd.fisparepart = sp.idsparepart
WHERE  sp.sparepartname = '1270-3132'
       AND dd.gsp_part_price IS NOT NULL
ORDER BY Claim_Submitted_Date DESC

This query returns the price immediately for most spareparts but some parts need several minutes to return NULL. So this query returns 940 rows immediately for the same part as above where every gsp_part_price is NULL which is the reason for the NULL above:

SELECT dd.*, sp.*
FROM tabdatadetail dd
INNER JOIN tabsparepart sp
    on dd.fisparepart=sp.idsparepart
WHERE sp.sparepartname='1270-3132'

Why does the first query needs 3 minutes to return NULL whereas the query below returns immediately the 940 rows? I must admit that i've still problems to interpret execution plans.

Note that the first query also returns immediately if i remove the IS NOT NULL CHECK:

dd.gsp_part_price IS NOT NULL

All involved tables are linked via foreign-keys, i have tried to add an index on  gsp_part_price which had no effect.

Here's the query execution plan:

Edit: Here are the stats on the PK and FK (to parent table tabData) indexes:

DBCC SHOW_STATISTICS('dbo.tabDataDetail','PK_tabDataDetail') WITH STAT_HEADER;
PK_tabDataDetail    Nov  6 2013 10:04AM 64327191    387089  183 1   8   NO

DBCC SHOW_STATISTICS('dbo.tabDataDetail','IX_fiData') WITH STAT_HEADER;
IX_fiData   Nov  6 2013 10:04AM 64327191    378150  198 0,2441889   4   NO

DBCC SHOW_STATISTICS('dbo.tabDataDetail','IX_GspPartPrice') WITH STAT_HEADER;
IX_GspPartPrice Dez  3 2013 12:17PM 64788799    64788799    200 0,0002433512    7,065503    NO

The last index was added just now as mentioned above. So the actual rows are 64788799, why the great difference between the estimated and actual rows, also , why is there a difference between the query-plan's actual rows and the "real" actual rows?

Here's the complete plan: http://s000.tinyupload.com/index.php?file_id=86912774678831839436

Edit: from the comment of Mikael Eriksson:

  I think your top(1) and order by is to blame here. The index
  scan is using the index on Claim_Submitted_Date to find the first
  occurrence of a value as fast as possible. SQL Server thinks that you
  will find such value after scanning 3000 rows. Perhaps that estimate
  makes sense if you have 5000 rows in tabSparePart and you only want
  one. The thing is that if there are no rows found or you have a spare
  part that is just recently submitted, SQL Server will have to scan the
  entire index before it figures out that there is no rows to be found.

Although i still don't understand why, it seems that this is true. If i remove the TOP 1 or change it to say TOP 1000 the result comes immediately. Does anybody have an  idea what i can do? Because, if multiple rows are returned i just need the last price submitted.

The query takes a lot of time because it finds no rows.

The plan first is setup top pick one row from tabSparePart where sparepartname = '1270-3132'. It then moves along to the index scan of tabData. For each row fetched from tabData there is a clustered index seek in locSL followed by a clustered index seek in tabDataDetail which also checks for not null on gsp_part_price and finally returns the values of fiSparePart and GSP_Part_Price. fiSparePart is then compared against the idSparePart returned from the Index Seek in tabSparePart. If they are the same the query immediately terminates and return the gsp_part_price. If they are not the same the index scan in tabData moves to the next record and all the checks are executed again. That keeps happening until there is a row returned or all rows in tabData have been processed.

As it turns out you get a totally different query plan when you do top(1000).

You can use the optimize for hint to make SQL Server think you actually want 1000 rows when you only need one.

Your scalar valued function would then look something like this:

CREATE FUNCTION [dbo].[FunctionName]()
RETURNS INT
AS
BEGIN

  DECLARE @TOP INT
  DECLARE @RET INT

  SET @TOP = 1

  SELECT TOP (@TOP) @RET = dd.gsp_part_price
  FROM  tabdata
          INNER JOIN locsl
                  ON tabdata.fisl = locsl.idsl
          INNER JOIN locgsp
                  ON locsl.figsp = locgsp.idgsp
          INNER JOIN tabdatadetail dd
                  ON dd.fidata = tabdata.iddata
          INNER JOIN tabsparepart sp
                  ON dd.fisparepart = sp.idsparepart
  WHERE  sp.sparepartname = '1270-3132'
          AND dd.gsp_part_price IS NOT NULL
  ORDER BY Claim_Submitted_Date DESC
  OPTION (OPTIMIZE FOR(@TOP = 1000))

  RETURN @RET

END


Enabling trace flag 8048 My database is running on SQL Server 2012 with 32 core processors. As per sp_blitz (by Brent Ozar) we need to enable trace flag 8048.
Should we enable this flag on 2012, or it is recommended only for 2014 onwards?

Microsoft's Bob Dorr wrote that trace flag 8048 is no longer required for SQL Server 2016 and newer:

  This means that trace flag -T8048 is no longer valid or needed on SQL Server 2016 because the design is such that SQL Server dynamically adjusts to CMemThread wait activities in order to optimize performance.

However, the behavior it fixes (CMEMTHREAD waits) is still relevent for 2014 and earlier versions.


Is the subscription of merge replication only for SQL Server Compact Edition? While creating a publication I saw this

Does this mean the subscribers can't be other editions of SQL Server but Compact?
Do other editions need to use Peer-to-Peer replication instead?
Thanks

This only means that if you are using SQL Server Compact edition, that this edition can only be a subscriber, not a publisher.

Here is more information on setting up merge replication between Microsoft SQL Server and SQL Server Compact 3.5.

Here is an example of a different subscriber than SQL Server Compact edition, a blogpost which sets up a Merge replication using SQL Server 2012 as the publisher and SQL Server 2008 R2 as the subscriber:

Merge Replication Step By Step


As a DBA, how would I go about transitioning from Oracle to SQL Server? I'm an Oracle DBA that also has Sybase experience.

What are the major architectural and conceptual differences between the two RDBMS platforms?

An answer similar to the SQL Server->Oracle question here would be of most use.

I've swapped between working on Oracle and SQL Server over the past few years, and wrote a blurb on going the other way here.  There are a number of idiomatic and architectural differences, and various bits of terminology get used differently by the vendor and developer/DBA communities surrounding each product.

Physical architecture

SQL Server organises various things a bit differently to Oracle and has one or two key concepts that have no direct analogues in Oracle.

A 'Database' is a separate item in SQL Server, with its own user permissions, schemas/name spaces and storage.  If you're familiar with Sybase, they work much the same as databases in Sybase, due to the common origins of the product.
Filegroups are roughly equivalent to table spaces, although they are local to a database.
A schema is a distinct concept from a database user in SQL Server, although users can have a default schema.
MVCC works somewhat differently in SQL Server.  It's a relatively recent feature, maintaining different copies of a row until the locks on the old version are released.  SQL Server has no direct equivalent to a rollback segment.  It is not active by default on SQL Server databases.
Tempdb is much more heavily used in SQL Server.  The system uses it for temporary tables and intermediate join results.  More on tempdb later.
Table partitioning is somewhat clumsier than Oracle.  You need to set up a partition function that creates a partition key from whatever you are supplying it, then a partition scheme over that partition function.  The partition scheme behaves a bit like a filegroup in that you then create the table on the partition scheme.Swapping partitions in and out requires you to set up a constraint on an empty table in the right structure.  The constraint guarantees that the partition key values are appropriate to the partition you intend to swap into it.
Materialised views are called indexed views in SQL Server.  The GROUP BY clause does have a CUBE operator, and the documentation alludes to a query rewrite feature.  However, this functionality is not well documented and may not be terribly mature.  YMMV.
SQL Server does not support autonomous transactions, although it does support two-phase commit through XA or OLEDB transaction protocols.
Clustered indexes are slightly different from index-ordered tables in Oracle, as they don't require all of the columns in the table to participate in the clustered index.  They're much more widely used in SQL Server architecture than IOTs are in Oracle.
SQL Server does support covering indexes, but doesn't have join indexes.  Bitmap indexes are not supported, although it does have an index intersection/star transform operator that can calculate intersections without hitting the fact table.
Sequences are a relatively recent addition to SQL Server.  Traditionally autoincrementing keys are done through identity columns.  You can load values into an identity column through set identity_insert on.

Programming

Idiomatic T-SQL has some differences to idiomatic PL/SQL.  It works differently enough that some of the paradigmatic differences merit explaining in more depth.

T-SQL has no concept of a package.  All stored procedures and functions in a database live in a common namespace, although schemas can be used to break this up, and the namespace is local to a database.
Get a feel for how to use temporary tables, and SELECT INTO.  It's pretty rare to encounter T-SQL code that actually needs a cursor; temporary tables allow operations to be broken up into steps that can be done with set operations.  SELECT INTO in tempdb is minimally logged, and it is minimally logged in certain recovery modes on user databases as well, so it is just as fast as query operator that persists an intermediate join result.Idiomatic T-SQL will use temp tables in the sort of roles that you would see cursor variables in PL/SQL, but will make much more use of set operations.  Temp tables can, however make for fairly obtuse code, so use with care.
The system data dictionary was much more obtuse than Oracle's one on older versions, but it got much better with SQL Server 2005.  Although the tools supplied by Microsoft have quite a lot of introspection stuff built in in the SSMS explorer, it's still worth knowing your way around the data dictionary.  It doesn't differentiate between ALL, USER and DBA views of the DB objects, though.
SSMS has a query plan viewer built in.
Identifiers in T-SQL code can be quoted with [], and can contain all sorts of rubbish if quoted.  However, if we catch you calling a column 'Direct/Transfer', we will rip your intestines out.
SQL Server does have window functions (since 2005 IIRC), so you can do ordering, running sums and suchlike within groups now.
T-SQL has no direct equivalent to CONNECT BY, although recursion can be done through recursive CTEs.
If you need to write code that hops across databases (as opposed to schemas within a database), consider using public synonyms to alias the objects to something local and refer to the aliases in the code.  This avoids hard-coded dependencies on database names.
If you avoid hard coded dependencies on database names, databases make it quite easy to maintain multiple environments on the same server.
Some things, such as custom aggregate functions, can only be implemented using CLR sprocs.  Also, if you want to escape from a transaction context (e.g. to fake an autonomous transaction for rollback-proof error logging) you can use a CLR sproc, as it can create a local connection outside of the current transaction context.

Security

Logins are defined at the SQL Server instance level, but each login maps to zero or more databases as a 'database user'.  Permissions cam be assigned to both 'logins' (server) and 'users' (database) but in a database 'roles' are normally used. Users belong to roles, permissions are assigned to roles. SQL Server 2012 adds 'server roles'.

SQL Server 2012 introduces a concept called 'partially contained databases', which allows user and role information to be held local to that database.
Within a database, the concept of user and schema is separated.  A user or role can be assigned to a schema and a schema owns database objects.
Windows authentication uses login information behind the scenes to authenticate a user on a machine or domain to a SQL Server login.  IIRC support for this is an optional extra on Oracle.
A special role, 'dbo' (short for 'database owner') has a sort of super-user privilege within a specific database.  Each database has a 'dbo' role and users can be assigned to the 'dbo' role on a given database.
There is also a default 'dbo' schema.  Objects may be owned by the dbo schema - Objects created by users with the 'dbo' role (or system-wide admin permissions) will default to being owned by the 'dbo' schema unless another schema is supplied explicitly.
Security information is not kept with a backup of a single database.  Users must and roles must be configured explicitly on the server the backup is restored to.  SQL Server 2012 allows user and role data to be kept locally to a database with a new 'partially contained databases' feature.
From SQL Server 2005, stored procedures can be executed in the security context of the caller, the creator, the owning schema or a specified user.
In a view on SQL Server, permissions on underlying tables are based on the permissions of the schema that owns the view.  User permissions on the underlying tables do not participate in the security, although a view definition can include filters that get information from the session.  In Oracle, user permissions on the underlying tables may affect the view, depending on the configuration of the grants.

Monitoring and tuning

TBA - memory architecture vs. SGA etc. in oracle

Backup and Recovery

TBA

Tooling

Microsoft bundles a set of surrounding tools with SQL Server.  Some of the major items supplied are:

SQL Server Management Studio (SSMS): This does something similar to SQL Developer on Oracle - it provides an editor and code execution facility.  Some useful features include a database object browser and a query plan viewer.
SQL Server Analysis Services (SSAS): This is an OLAP server that is distinct from the database server.  It uses its own query language (MDX) and API (XML/A) for client-server communication.  It cannot be queried with SQL.  SSMS has a facility for editing MDX and raw XMLA queries and displaying the results.  A command line query tool called ASCMD.EXE is also supplied.
SQL Server Reporting Services (SSRS): This is a web-based reporting tool for publishing reports.  Reports can be built through BI Development Studio (BIDS) or Report Builder, and published to a web portal.  The SSRS server itself has a web service API for programatically managing the server.  Note that SSRS reports can consume data from a variety of sources, not just SQL Server.  A command line tool called RS.EXE is supplied for programatically managing SSRS servers.
SQL Server Integration Services (SSIS): This is an ETL tool supplied with SQL Server.  Architecturally, it is quite different to OWB or ODI in that it is not a code generation tool.  The runtime sits client-side and can be on a separate machine to the database server.  SSIS packages can be developed with BIDS and executed independently with a command line tool called DTEXEC.EXE.
B.I. Development Studio (BIDS): This is a visual studio based environnemt for developing reports, SSIS packages and SSAS cubes.  If other VS based development tooling is installed (e.g. VS Professional) the tooling can be integrated into a single environment and a common project grouping.
Bulk copy (BCP): A command line bulk insert/extract tool similar to SQL*Loader
SQLCMD: A command line query tool similar to SQL*plus
SQL Profiler: A tracing and profiling tool that can capture and evaluate trace information from SQL Server, SSAS and other tools in the suite.
SQL Server Agent: A job scheduling utility that can run periodic jobs of one sort of another.


what are memory fractions and how to get rid of them (or the sort operator) in the example below? This execution plan has the following memory fractions:
Memory fraction input:1, Memory Fraction Output:1.

it is a very simple query:

SELECT                  [a].[activityId],
                        [a].[title],
                        [a].[description],
                        [a].[inclusions],
                        [d].[path],
                        [d].[uploadToBucket],
                        [a].[imageUriMain],
                        [a].[locationId]
FROM                    [dbo].[activity]                AS a
LEFT JOIN               [dbo].[document]                AS d
       ON d.documentId = a.documentId
      AND d.activityId = a.activityId

ORDER BY                title                           ASC

what are memory fractions and how to get rid of them (or the sort operator) in the above example?

Having a look at the properties of the sort operator:
it has MemoryFractions Input="1" Output="1"

getting rid of this sort operation

having a look at the indexes and definition of table dbo.activity
there are no nonclustered indexes only the primary key

CREATE TABLE [dbo].[activity] (
[activityId]      INT              IDENTITY(1,1)                 NOT NULL,
[title]           VARCHAR(100)                     NOT NULL,
[description]     VARCHAR(max)                     NOT NULL,
[inclusions]      VARCHAR(max)                         NULL,
[imageUriMain]    VARCHAR(255)                         NULL,
[imageUriThumb]   VARCHAR(255)                         NULL,
[imageUriTeaser]  VARCHAR(255)                         NULL,
[categoryId]      INT                              NOT NULL  CONSTRAINT [DF__activity__catego__0F975522] DEFAULT ((1)),
[locationId]      INT                                  NULL,
[documentId]      INT                                  NULL,
CONSTRAINT   [PK_activity]  PRIMARY KEY CLUSTERED    ([activityId] asc),
CONSTRAINT   [FK_activity_category]                         FOREIGN KEY ([categoryId]) REFERENCES [ref_activityCategory]([categoryId]),
CONSTRAINT   [FK_activity_location]                         FOREIGN KEY ([locationId]) REFERENCES [location]([locationId]))

GO

same with table documents

CREATE TABLE [dbo].[document] (
[documentId]      INT              IDENTITY(1,1)                 NOT NULL,
[uploadToBucket]  VARCHAR(200)                         NULL,
[path]            VARCHAR(200)                         NULL,
[activityId]      INT                                  NULL,
CONSTRAINT   [PK__document__EFAAAD856EBBBDCD]  PRIMARY KEY CLUSTERED    ([documentId] asc))

I create the following indexes (paying attention to the order by and all the needed columns for this query):

create index I_title_01 on [dbo].[activity]
(title asc,documentId  asc,activityid asc)
INCLUDE(
        [description],
        [inclusions],
        [imageUriMain],
        [locationId]
)
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, --drop_existing=on,
DATA_COMPRESSION=page, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON NONCLUSTERED_INDEXES

create index I_doc_01 on [dbo].[document]
(documentId  asc,activityid asc)
INCLUDE(
                            [path],
                            [uploadToBucket]
)
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF,
 --drop_existing=on,
DATA_COMPRESSION=page, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON NONCLUSTERED_INDEXES

And now the new execution plan looks like this (without sort operation):

Regarding what are memory fractions, I get my answer from this site below, which has explanations and even examples:

Batch mode memory fractions

  There’s very little information out there about memory fractions. I
  would define them as information in the query plan that can give you
  clues about each operator’s share of the total query memory grant.
  This is naturally more complicated for query plans that insert into
  tables with columnstore indexes but that won’t be covered here. Most
  references will tell you not to worry about memory fractions or that
  they aren’t useful most of the time. Out of thousands of queries that
  I’ve tuned I can only think of a few for which memory fractions were
  relevant. Sometimes queries spill to tempdb even though SQL Server
  reports that a lot of query memory was unused. In these situations I
  generally hope for a poor cardinality estimate which leads to a memory
  fraction which is too low for the spilling operator. If fixing the
  cardinality estimate doesn’t prevent the spill then things can get a
  lot more complicated, assuming that you don’t just give up.


How do I prevent SQLite database locks? From SQLite FAQ I've known that:

  Multiple processes can have the same database open at the same time.
  Multiple processes can be doing a SELECT at the same time. But only
  one process can be making changes to the database at any moment in
  time, however.

So, as far as I understand I can:
1) Read db from multiple threads (SELECT)
2) Read db from multiple threads (SELECT) and write from single thread (CREATE, INSERT, DELETE)

But, I read about Write-Ahead Logging that  provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

Finally, I've got completely muddled when I found it, when specified:

  Here are other reasons for getting an SQLITE_LOCKED error:

  Trying to CREATE or DROP a table or index while a SELECT statement is
  still pending.
  Trying to write to a table while a SELECT is active on that same table.
  Trying to do two SELECT on the same table at the same time in a
  multithread application, if sqlite is not set to do so.
  fcntl(3,F_SETLK call on DB file fails. This could be caused by an NFS locking
  issue, for example. One solution for this issue, is to mv the DB away,
  and copy it back so that it has a new Inode value

So, I would like to clarify for myself, it is necessary to avoid the lock? Can I read and write at the same time from two different threads? Thanks.

That page you linked, besides being quite old, talks about accesses from the same process through the same database connection (or through multipe connections in shared cache mode, which you should not use).

When not in WAL mode, multiple connections can read from the same database, but a writing transaction is exclusive, i.e., no other readers or writers are allowed.

In WAL mode, a writer and readers do not block each other, but there is still only one writer allowed.


mysql innodb cluster 3 nodes, 2 lagging almost 24 hours I have a 3 node setup and the 2 read only nodes are way behind (like 24 hours) from the rw node..

I saw here:  https://bugs.mysql.com/bug.php?id=86797 to restart the lagging node and will pick up by itself..
since I was not very convinced I did this in just one node..
restarted ok and came to the cluster in "RECOVERING" mode
such good...
But now I am checking and the Q is super big getting bigger
and when I compare the data in one table to the one in RW is updating.. just very slow like one a sec .. so is not picking up any slack.. it should go faster to pick up with the RW node right?

 CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 15255041(deleted)
                         MEMBER_ID: 47dbb651-XXXXX (deleted)
       COUNT_TRANSACTIONS_IN_QUEUE: 218417
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1667fe4c-5033-11e8-b942-(Deleted)-3140512,
617916a6-5032-11e8-ad5b-(Deleted):1-14
    LAST_CONFLICT_FREE_TRANSACTION:

note: I added the deleted for privacy.
on my other node is also behind around 24 hours says Q is 0

select * from replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 1525504100(Deleted):13
                         MEMBER_ID: 617916a6-5032-11e8-(deleted)
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 6846224
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 5921012
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1667fe4c-5033-11e8-b942-0cc(deleted):1-3173566,
617916a6-5032-11e8-ad5b-(deleted):1-14
    LAST_CONFLICT_FREE_TRANSACTION: 1667fe4c-5033-11e8-b942-0(deleted)
1 row in set (0.0004 sec)

I am researching but is confusing some is for mysql 8.x like this solution:
http://lefred.be/content/mysql-innodb-cluster-how-to-handle-fc/

and other info I found is for master/slave so is not related..

Also looking into the parallel slave options but not sure if this will make any difference.

NOTE: the hardware is exactly the same on all 3 nodes.

thanks

Hello for those out there with similar issues I fixed it thanks to this amazing blog post.
https://thoughts.t37.net/fixing-a-very-lagging-mysql-replication-db6eb5a6e15d
basically my changes:

STOP SLAVE;
SET GLOBAL slave_parallel_workers=40;
START SLAVE;
set GLOBAL sync_binlog=0;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
SET GLOBAL innodb_flush_log_at_timeout=1800;`

I already had the MT setup so just needed to add this and 40 workers.
Hope this will help other people..


TSQL SELECT statement to list a column calculated based on the value of a field I have a table named Table1, with three fields Project ID, EmployeeID, Starting Date.
Need a select statement to list all columns of the table, and an additional column to show the number of project by employee since a year. See below figure.

It should be enough to get the count(distinct EmployeeID) where StartingDate > 1/1/2016. But, I can't figure out the query. Could anyone please help?

If the project id is distinct this is easy to achieve with two window functions. Maybe not the best performance but just flows out of my fingers. Feel free to improve:

Setting up

CREATE TABLE #Table1
(
 ProjectID int,
 EmployeeID int,
 StartingDate DateTime
);

INSERT INTO #Table1 (ProjectID, EmployeeID, StartingDate)
VALUES (1, 1001, '1/1/2005'),
       (2, 1003, '1/1/2019'),
       (3, 1007, '1/1/2012'),
       (4, 1001, '1/1/2017'),
       (5, 1002, '1/1/2018'),
       (6, 1003, '1/1/2015'),
       (7, 1001, '1/1/2016'),
       (8, 1002, '1/1/2018'),
       (9, 1004, '1/1/2018'),
       (10, 10001, '1/1/2018');

The query;

WITH cte_Count
AS
(
  SELECT t.ProjectID,
         COUNT(*) OVER (PARTITION BY t.EmployeeID ORDER BY t.StartingDate) AS project_count
  FROM #Table1 t
  WHERE t.StartingDate > '1/1/2016'
)
SELECT t.ProjectID,
       t.EmployeeID,
       t.StartingDate,
       MAX(project_count) OVER (PARTITION BY t.EmployeeID) AS project_count_since_2016
FROM #Table1 t
LEFT JOIN cte_Count c ON c.ProjectID = t.ProjectID

The first part just filters the projects that are not in the range you want. The second joins this information with the source table.


Improve Insert performance for 200 Million rows How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days.

SELECT DISTINCT
    od.SourceID,
    od.OrderID,
    od.OrderLineNo,
    CASE WHEN mbew.vprsv = 'S' THEN
        CASE WHEN isNumeric(mbew.stprs) = '1' THEN convert(decimal (15, 2), mbew.stprs)/peinh ELSE NULL END
    WHEN mbew.vprsv = 'V' THEN
        CASE WHEN isNumeric(mbew.verpr) = '1' THEN convert(decimal (15, 2), mbew.verpr)/peinh ELSE NULL END
    ELSE NULL END as standardPrice,
    mbew.vprsv,
    mbew.stprs,
    mbew.verpr,
    mbew.peinh,
    od.currencyCode,
    CAST(exchangerate1 AS FLOAT) * (CAST(erf.toFactor AS FLOAT) / CAST(erf.fromFactor AS FLOAT)) as exchangerate1,
    t001.waers,
    'X3' as xfactory

into TempCostTable

FROM Order_Detail od (nolock)

INNER JOIN Order_Header oh  (nolock)
    ON od.SourceID = oh.SourceID
    AND od.OrderID = oh.OrderID

INNER JOIN eord   (nolock)
    ON eord.matnr = od.ProductID
    and eord.flifn = 'X'

INNER JOIN zsm_eqmupd_cntrl zsm2    (nolock)
    ON eord.lifnr = 'IVG'+ zsm2.werks
    AND zsm2.xfactory = 'X'

INNER JOIN mbew mbew   (nolock)
    ON mbew.matnr = eord.matnr
    AND eord.lifnr = 'IVG' +  mbew.bwkey
    AND mbew.lvorm != 'X'

INNER JOIN t001k   (nolock)
    ON mbew.bwkey = t001k.bwkey

INNER JOIN t001  (nolock)
    ON t001k.bukrs = t001.bukrs

LEFT JOIN ExchangeRate_Detail exchrt (nolock)
    ON exchrt.SourceID = '000001'
    AND t001.waers = exchrt.currencycode
    AND od.currencyCode = exchrt.ToCurrencyCode
    AND CASE WHEN isdate(oh.orderDate) = 0 THEN getdate() ELSE oh.orderDate END BETWEEN exchrt.StartDate AND exchrt.EndDate

LEFT JOIN ExchangeRate_Factor erf (nolock)
    ON erf.SourceID = '000001'
    AND t001.waers = erf.currencycode
    AND od.currencyCode = erf.ToCurrencyCode
    AND CASE WHEN isdate(oh.orderDate) IN ('0', '00000000') THEN getdate() ELSE oh.orderDate END BETWEEN erf.StartDate AND erf.EndDate

WHERE od.SourceID = '000001'

So many things...

If you have the Actual Execution Plan, please post it. I'm curious to see what indexes you have, and what the shape of the data is.

In the meantime, be aware that:

ON eord.lifnr = 'IVG'+ zsm2.werks

...isn't great for finding rows in zsm2. You may want to add:

AND eord.lifnr LIKE 'IVG%'
AND zsm2.werks = STUFF(eord.lifnr,1,3,'')

And you may as well put:

AND mbew.bwkey = zsm2.werks

Your types look like they need some attention, and of course your indexing strategy is bound to need some attention.

EDITED FOLLOWING PLAN UPLOAD

So, now that I've seen the plan. Yeah, it's ugly.

You should consider what is going to be a useful method for joining your main tables, and how you'd do this if you were solving it on paper.

Try indexes on both Order_Detail and Order_Header, on (SourceID, OrderID), including all the other columns that are needed. This will allow a Merge Join between those tables. Then making sure that you can quickly access the rows in each subsequent table, and you have a relatively good candidate for a nicer plan.

eg: an index on eord (flitnr, matnr, lifnr) --with the LIKE 'IVG%' thing I mentioned above.

There's going to be a bunch of pain around those join clauses with CASE. If you need it to be joining to exactly one, you may prefer to use OUTER APPLY (SELECT TOP (1) ... WHERE StartDate < CASE... oh.OrderDate...END ORDER BY StartDate DESC), with an index on (SourceID, CurrencyCode, ToCurrencyCode, StartDate DESC)... you don't really need EndDate.

I've seen worse, and at the end of the day, this should be quite tunable.


db2 executing command crash shell I have a simple ksh script that executes db2 queries, such as

#!/bin/ksh
#some initialization
#step1,2,3
set -e
db2 -x "select * from table a "
#step4

The main issue seems to be caused by the db2 command. If the query returns records the shell script works perfectly fine. But if the query returns no results, then step4 will not be executed at all, and exits the shell immediately. This doesn't make sense. If the query didn't return anything, the shell still shouldn't crash anyway.

Can someone explain the reason ?

it turns out that there is a option "set -e" in the long script which will capture all unhandled execptional scenarios and abort the shell execution.

I guess it has something to do with the fact, that db2 will return an error code of 1 when no records were found.


MyISAM recover updated row The row (with type MEDIUMTEXT) has been accidentally updated with empty value. I can see previous data in MYD file of a table, but I can't get them neither from mysqldump or any mysql client. If I execute mysqlcheck -u root -p --auto-repair --check --optimize the MYD-file shrinks from 1688484 bytes to 449008 bytes.

Is there any way to export data from MYD or mark data as undeleted?

Version from dump is: MySQL dump 10.13  Distrib 5.1.37, for pc-solaris2.11 (i386)

Unfortunately the only way to recover your data is to recover them from a backup, you can import just that single table and then insert the particular row.

I will edit my answer to get more precise here, first of all you write that you use dynamic row length. This makes the recover operation way harder if not impossible compared to fixed row length. If you are seriously want to recover, I would suggest that you first of all get known to MyISAM dynmic data file layout, a good entry point would be here: MyISAM Dynamic Data File Layout. Therefore you have a chance that you can recover, but not with a tool which is written for fixed length files. Unfortunately I am not aware of such a tool (the ones i tested didn´t satisfied my requirements).

And btw, I think you will have heard or read that more than enough already, but i want to stress this out again: always backup, test the backups, rinse and repeat.


Search is slow in a partitioned table I'm running MySQL 5.7.15 on Ubuntu server 16.04.

After I've partitioned a table by range, select queries for some values are slow. I've checked that queries are slow if key is missing in older partition.

CREATE TABLE `table` (
  `id` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0',
  KEY `table_1` (`id`,`clock`),
  KEY `clock` (`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p2016_10_16 VALUES LESS THAN (1476655200) ENGINE = InnoDB,
 PARTITION p2016_10_17 VALUES LESS THAN (1476741600) ENGINE = InnoDB,
 PARTITION p2016_10_18 VALUES LESS THAN (1476828000) ENGINE = InnoDB,
 PARTITION p2016_10_19 VALUES LESS THAN (1476914400) ENGINE = InnoDB,
 PARTITION p2016_10_20 VALUES LESS THAN (1477000800) ENGINE = InnoDB,
 PARTITION p2016_10_21 VALUES LESS THAN (1477087200) ENGINE = InnoDB) */|

Executing the query:

select *
from table
where id='25885'
and clock>1476044179
order by clock desc
limit 3;

...takes over 1 second, maybe because there are only entries (15 tuples) in the last partition.

If I instead execute:

select *
from table
where id='24946'
and clock>1476044179
order by clock desc
limit 3;

...the query is faster (much faster) maybe because there are entries in all partitions (8k tuples).

What am I missing?

You seem to have an issue with key distribution with id and the Query Optimizer.
Since the id is not being factored into the partitioning scheme, I only see MySQL having to look up the id up to 6 times. Depending on when and how frequent each id value is written, the query could be using the wrong index.
There are two things you could do to see if id key distribution is at issue
SUGGESTION #1 : Run EXPLAIN on each query
explain select *
from table
where id='25885'
and clock>1476044179
order by clock desc
limit 3;
explain select *
from table
where id='24946'
and clock>1476044179
order by clock desc
limit 3;

The output will tell you what index was chosen to scan for rows. If the clock index is being used, then the clock is being scanned in the index, but the id is being scanned from the table.
SUGGESTION #2 : Change the ORDER BY
If the order by clock desc is making the Query Optimizer choose the clock index, try this:
order by id,clock desc

That way, the scan will be from table_1 index, starting from the last time anid was inserted.
GIVE IT A TRY !!!