Joining temporal and spatial data I have two tables in psql that I am trying to join based on certain attributes - they are very large - 17 M rows and 2.7 M rows respectively

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='foo';
 estimate
----------
17087196
(1 row)

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='bar';
 estimate
----------
  2763829
(1 row)

On both, I have created spatial indices with

CREATE INDEX foo_gix ON foo USING GIST (the_geom);

The query I am running is a point in polygon analysis based on data collected at historical time intervals - counting points in polygons when the timestamps on both tables match.  Points are locations of mobile phone connections (bar_history and bar) and polygons are buffers around certain areas (foo_history, foo)

It looks like this:

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
group by s.id LIMIT 5

This query returns results successfully in less than a few seconds.  However, when I add join by timestamps:

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
AND
to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) =
to_timestamp(floor((extract('epoch' from s.time::timestamp) / 600 )) * 600)
    group by s.id LIMIT 5

the query runs endlessly, even though nearly all of the timestamps will match each other from both tables.  I am not a database expert and would appreciate any suggestions on how to speed up this query or add some kind of index that might speed it up.

Time

This is really bad:

AND
to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) =
to_timestamp(floor((extract('epoch' from s.time::timestamp) / 600 )) * 600)

Instead, try to rewrite that in terms of l.time, ignoring epoch. I have no idea of the interval you want.

AND l.time BETWEEN s.time AND (s.time + '10 minutes');

Or whatever. If you're trying to assign arbitrary timestamps into 10 minutes intervals or whatever, there is no need to cast back to timestamp. You may consider writing your own function for convenience,

CREATE FUNCTION myIntervalExtract(ts timestamp with time zone)
RETURNS int
AS $$
  SELECT floor( extract(epoch FROM l.time::timestamp) / 600 )::int * 600;
$$ LANGUAGE sql
IMMUTABLE;

Now you can create a functional index on both myIntervalExtract(l.time) and myIntervalExtract(s.time) using the above. That will satisfy the join so long as you join

FROM l
JOIN s ON myIntervalExtract(l.time) = myIntervalExtract(s.time)

GIS

ST_Contains(ST_Buffer()..)

This is also an antipattern. You'll want to rewrite that to ST_DWithin. you should also aim not to have ST_Transform in your join conditions. Instead, consider using geography on the table.

FROM t1 JOIN t2 ON ST_DWithin(t1.polygeog, t2.geogpoint, distance_in_meters)


How to restore Network backup to Network Path in SQL Server 2005? I am trying to restore network backup file to network path.. is there any way to do it?

I am using SQL Server 2005 and code....

RESTORE DATABASE DB_NAme
FROM DISK = N'\\NetworkPath\backup_20140501030004.BAK'
WITH  FILE = 1
, MOVE 'DB_Data' TO '\\NetworkPath\DATA\DB.mdf'
, MOVE 'DB_Data2' TO '\\NetworkPath\DATA\DB_1.mdf'
, MOVE 'DB_Data3' TO '\\NetworkPath\DATA\DB_2.mdf'
, MOVE 'DB_Log' TO '\\NetworkPath\Log\DB_log.ldf'
, NOUNLOAD,REPLACE, STATS = 10;

Error:

  Msg 5110, Level 16, State 2, Line 2
  The file "\NetworkPath\DATA\DB.mdf" is on a network path that is not supported for database files.
  Msg 3156, Level 16, State 3, Line 2
  File 'DB_Data' cannot be restored to '\NetworkPath\DATA\DB.mdf'. Use WITH MOVE to identify a valid location for the file.

Thanks in advance

Microsoft has documented their "Description of support for network database files in SQL Server" at: http://support.microsoft.com/kb/304261

Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. However, this is not without risk and you can fairly easily wind up with a corrupt database if the device does not fully support what SQL Server needs to achieve consistency.

If you want to use this approach in a manner that Microsoft will fully support, they have a set of standards describing what they support.  Following is a quote from the link:

Windows Hardware Quality Lab (WHQL) qualified devices
Microsoft Windows servers and networked servers or NAS storage servers that are Windows Hardware Quality Lab (WHQL) certified automatically meet the data write ordering and write-through guarantees required to support a SQL Server storage device. Microsoft supports both application and storage-related issues in these configurations.

Note To be supported by SQL Server, the NAS storage solution should also meet all the requirements that are listed on the following Microsoft website:
http://download.microsoft.com/download/6/E/8/6E882A06-B71B-4642-9EB4-D1EA0D6223C8/SQL%20Server%20IO%20Reliability%20Program%20Requirements%20Document.docx


Oracle: How to AUTOTRACE in my program You can display the results of EXPLAIN PLAN with

explain plan for select * from foo;
select * from table(dbms_xplan.display);

How can I similarly display the results for an AUTOTRACE in my program?

You'll have to write your own, it's not a native feature of oracle, it's something that has been implemented in Oracle tools. Grab the plan from v$sql_plan for your SQL_ID and then grab the delta of session stats before and after you run your query.

It's a little more involved in that, but that's the basic mechanics.


Convert Varchar To Datetime and Add Seconds I have the following two columns in my table

callstarttime       callduration
20180101215910        120
20180101215710        220

The first column's data type is varchar(255) whilst the second column is int.

What I want is to add the callduration to the callstarttime inorder to get the time which the call ended.

So I need to first convert the callstarttime column to datetime and then use the Dateadd function.

My script is as follows:

select convert(datetime, callstarttime) from Mytable

I have also tried using cast as follows

select cast(callstarttime as datetime) from Mytable

However I am getting the following error

Conversion failed when converting date and/or time from character string.

Similar questions have been asked on Convert varchar column to datetime and how to convert this varchar to datetime format?, however I can't tailor my script based on the solutions provided there.

Let me know how this works for you.

CREATE TABLE #t (callstarttime VARCHAR(255), callduration INT);
INSERT #t ( callstarttime, callduration )
SELECT *
FROM (
VALUES('20180101215910', 120),('20180101215710', 220)
) AS x (callstarttime, callduration);

WITH munge AS (
SELECT *,
    LEFT(callstarttime, 8) AS d,
    STUFF(STUFF(SUBSTRING(callstarttime, 9, LEN(callstarttime)) , 3, 0, ':'), 6, 0, ':') AS t
FROM #t AS t
)
SELECT *,
       TRY_CONVERT(DATETIME, d + ' ' + t),
       DATEADD(SECOND, munge.callduration, TRY_CONVERT(DATETIME, d + ' ' + t))
FROM munge


remove maintenance plan I have the following error while trying to delete some old Miantenance plans on the MS-SQL server:

Please help.

Ok. I discovered that the source of this problem is a Server upgrade where the server name was changed and thus the outbound SQL instance name changed.The maintenance plans are stored in the msdb system database and this is where they may be edited. So after some online search, I found this SP (which I have modified):

USE msdb go
BEGIN TRY
    DECLARE @errno INT, @errmsg varchar(100)    DECLARE @mpname varchar(200)

--Set the NAME of the Rogue Maintenance plan here!
SET @mpname = 'myPlanToDelete'

BEGIN TRAN

-- Delete the JobStep Logs for this plan's jobs
-- SELECT jl.*
DELETE dbo.sysjobstepslogs
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobsteps as js ON (sb.job_id = js.job_id)
    JOIN dbo.sysjobstepslogs as jl on (js.job_id = jl.step_uid)
 WHERE pk.name = @mpname

-- Delete the JobHistory for this plan's jobs
--SELECT jh.*
DELETE dbo.sysjobhistory
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobhistory as jh ON (sb.job_id = jh.job_id)
WHERE pk.name = @mpname

-- Delete the Job Schedules for this plan
--SELECT js.*
DELETE dbo.sysjobschedules
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobschedules AS js on(sb.schedule_id = js.schedule_id)
 WHERE pk.name = @mpname

-- Delete the JobSteps for this plan's jobs
--SELECT js.*
DELETE dbo.sysjobsteps
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobsteps as js ON (sb.job_id = js.job_id)
WHERE pk.name = @mpname

-- Delete the Jobs for this plan
--SELECT sj.*
DELETE dbo.sysjobsteps
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobs as sj ON (sb.job_id = sj.job_id)
WHERE pk.name = @mpname

-- Delete the Plan LOGS for this plan
--SELECT pl.*
DELETE dbo.sysmaintplan_log
FROM
    dbo.sysmaintplan_log as pl
    join dbo.sysdtspackages90 as pk ON (pk.id = pl.plan_id)
WHERE pk.name = @mpname

-- Delete the SUB-Plans for this Plan
--SELECT sp.*
DELETE dbo.sysmaintplan_subplans
FROM
    dbo.sysmaintplan_subplans as sp
    join dbo.sysdtspackages90 as pk ON (pk.id = sp.plan_id)
WHERE pk.name = @mpname

-- Delete the PLAN
-- SELECT *
DELETE dbo.sysdtspackages90
FROM dbo.sysdtspackages90
WHERE name = @mpname

-- All done!
COMMIT WORK

END TRY
BEGIN CATCH
    SELECT @errno = ERROR_NUMBER() ,@errmsg = ERROR_MESSAGE()
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN
    END
    RAISERROR(@errmsg,16,127)
END CATCH
GO

Thanks.


Monitoring Progress of Index Construction in PostgreSQL Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring.

Is there a way to monitor this?

According to Postgres Wiki's Index Maintenance page, you can find out the current state of all your indexes with:

SELECT
  t.tablename,
  indexname,
  c.reltuples AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
  CASE WHEN indisunique THEN 'Y'
    ELSE 'N'
  END AS UNIQUE,
  idx_scan AS number_of_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_tables t
  LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
  LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
      JOIN pg_class c ON c.oid = x.indrelid
      JOIN pg_class ipg ON ipg.oid = x.indexrelid
      JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
  ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

The column num_rows indicates how many rows are covered by your index and index_size will grow as the index is being built.


how to set bulk_insert_buffer_size in mysql? I want to set bulk_insert_buffer_size variable for insert. I searched from other pages too, all they are saying about my.cnf. But there is no my.cnf in my system. I checked my.ini and there is no such variable there.

I also tried through command line -

SET GLOBAL bulk_insert_buffer_size= 268435456;

It shows -

1 queries executed, 1 success, 0 errors, 0 warnings

Query: SET GLOBAL bulk_insert_buffer_size =1024*1024*256

0 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0.001 sec
Total Time     : 0.002 sec

but on running -

SHOW VARIABLES LIKE '%bulk%';

I am getting the same(old one) -

Variable_name            Value
bulk_insert_buffer_size  8388608

Please let me know if I am doing something wrong.
I am using Mysql 5.5 version.

Thank you.

When you ran

SET GLOBAL bulk_insert_buffer_size =1024*1024*256;

what you did was set the insert buffer to 256M for incoming DB Connections going forward.

To set it for your current DB session, run this in that session:

SET bulk_insert_buffer_size =1024*1024*256;

Then, you can run

SHOW VARIABLES LIKE '%bulk%';

and see a different value.

In a post made back on April 18, 2013 (see Steps 2 and 3), I suggested setting the bulk_insert_buffer_size to 256M within a current session.

If you want to keep that value when you restart mysql, just add this to my.cnf

[mysqld]
bulk_insert_buffer_size = 256M

Give it a Try !!!


Read Committed Snapshot Isolation vs Read Committed - pros and cons? We have an application from JD Edwards, for which the database runs on SQL Server 2005 or 2008R2.

They have a new application vendor and he suggested to change isolation level to Read Committed Snapshot Isolation (RCSI) instead of default locking Read Committed to resolve a blocking issue:

Long story short, client was complaining about getting error & we involve application team to look logs on app server & at that same time we see blocking on database so then we send the SPID & app team for rollback or kill that connection & issue will disappear so they have few API cursors & users searching for data every now & then. As a maintenance we are doing rebuild index & update stats on weekly basis.

We see hundreds of cursors running on server & I give the SPID of first blocking happened & then application team find out rest, but what i am seeing is below:

FETCH API_CURSOR0000000000A4804D
FETCH API_CURSOR0000000000A467E6
FETCH API_CURSOR00000000006AF7B0
Microsoft SQL Server JDBC Driver set transaction isolation level read committed

I told them by changing to RCSI, each update will hold a pointer if data is reading on that page or row, we also can get dirty reads and will use more tempdb in SQL Server. We have had a back and forth conversation and can't decide whether to make this change on the server.

Can anyone point out the pros and cons of using RCSI, or tell me if you have experience with the JD Edwards application running on SQL Server and best practices for the isolation level?

By default, SQL Server writers block readers and vice versa if Read Committed  Snapshot Isolation is not enabled. If the application is not written exclusively for SQL Server, it may cause lots of extra locks (SQL Server can also escalate locks) when database is used more or less extensively.
Enabling Read Committed Snapshot Isolation solves this issue and improves concurrency.

On the other hand, it adds load on tempdb (never a problem from my experience at least for SQL Server 2008R2 and higher) and may potentially cause some consistency issues for applications written for SQL Server only. Note, it has nothing to do with dirty reads.


Transaction log restore fails with error "Operating system error 5(Access is denied.)." I have backed up transaction logs on my database and trying to restore them on another instance. But when trying to restore I receive an error: Operating system error 5(Access is denied.) and RESTORE LOG is terminating abnormally.

I guess, it means SQL Server user does not have permission for this operation, but I have already granted him public and sysadmin server roles. Could you please advise what could be the reason for this error?

First Error

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device '\\serverX1\d$\trlogs' Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally1

Solution:
It means the service account of SQL Server (which used to start SQL Server service) does not have access to the source.

START > RUN > type SERVICES.MSC.
Find SQL Server Service > Right click and go to PROPERTIES.
Go to LogOn/Security tab

If it's system account (NETWORK SERVICE, LOCAL SYSTEM etc.) then change it to any specific account (domain account like ex. dba\sqlserviceact) and grant the account (ex. dba\sqlserviceact) read & write privilege on the shared folder (network path).

Second Error

Msg 3117, Level 16, State 1, Line 1 The log or differential backup cannot be restored because no files are ready to rollforward. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally

**Solution: **

Verify your differential has Differential Base LSN same as FirstLSN of Full Backup, using below query.

RESTORE HEADERONLY FROM DISK='\\PATH\Full_Backup.BAK'

RESTORE HEADERONLY FROM DISK='\\PATH\Diff_Backup.BAK'

If it's same, then First Restore Full backup with NORECOVERY.

    RESTORE DATABASE DatabaseName
    FROM DISK = '\\PATH\Backup_File_Full.bak'
    WITH NORECOVERY;

Then Restore Differential correspond to the full backup you restored with NORECOVERY

    RESTORE DATABASE DatabaseName
    FROM DISK = '\\PATH\Backup_File_Diff.bak'
    WITH NORECOVERY; --Use WITH RECOVERY if you need to recover data for the point of time

Now if you have Transaction Log backup to restore. Restore all the transaction log backup with NORECOVERY. Only Last T-Log backup (To the point of time you want to recover) will be restored with RECOVERY

To Restore Transaction Log Backup

RESTORE LOG DatabaseName FROM DISK = '\\Path\Backup_File_TLog.bak' WITH NORECOVERY; --If it's the last backup you need to restore use `WITH RECOVERY`

And your problem will be resolved.

Thanks


Syntax error in load data infile I don't know why I am getting a syntax error. I have checked my query carefully many times and read up on mysql reference for load data infile and everything seems fine. Here is my query:

LOAD DATA LOCAL INFILE '/tmp/phpnm6Zek' IGNORE INTO TABLE loaddata_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@sku, @model, @option, @option_value, @required, @quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model, option=@option, option_value=@option_value, required=@required, quantity=@quantity, subtract=@subtract, price=@price, points=@points, weight=@weight

This is the error that I am getting:

Notice: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option=@option, option_value=@option_value, required=@required, quantity=@quanti' at line 1
Error No: 1064
LOAD DATA LOCAL INFILE '/tmp/phpO6k4u3' IGNORE INTO TABLE loaddata_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@sku, @model, @option, @option_value, @required, @quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model, option=@option, option_value=@option_value, required=@required, quantity=@quantity, subtract=@subtract, price=@price, points=@points, weight=@weight

The word option is a reserved word :

You need to surround the word option with back quotes.

LOAD DATA LOCAL INFILE '/tmp/phpnm6Zek'
IGNORE INTO TABLE loaddata_temp
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (@sku, @model, @option, @option_value, @required,
@quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model,
`option`=@option, option_value=@option_value, required=@required, quantity=@quantity,
subtract=@subtract, price=@price, points=@points, weight=@weight

Give it a Try !!!


How to convert innodb engine tables to tokudb engine Hi I want to migarate my server newly installed tokudb engine. i want to convert each table of database from innodb to tokudb. How can I do that using sed command ?

Instead of using sed, why not let mysql write the conversion script for you?

echo "SET SQL_LOG_BIN = 0;" > Convert_InnoDB_to_TokuDB.sql
MYSQL_USER=root
MYSQL_PASS=password
MYSQL_USERPASS="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_PORT=3306
MYSQL_CONN="-h127.0.0.1 -P${MYSQL_PORT} ${MYSQL_USERPASS}"
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',"
SQLSTMT="${SQLSTMT} table_name,' ENGINE=TokuDB;') InnoDBConversionSQL"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='InnoDB'"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
MYSQL_OPTIONS="--skip-column-names -AN"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" >> Convert_InnoDB_to_TokuDB.sql
less Convert_InnoDB_to_TokuDB.sql

When you are satisfied with the conversion script, then run it

mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < Convert_InnoDB_to_TokuDB.sql

Give it a Try !!!


Error while Uninstalling SQL Server 2016 instance We have 3 instances on a windows 2012 server 1 instance of SQL Server 2012 & 2 instances of SQL Server 2016.

One of SQL Server 2016 instance is not in use, so I want to remove it.

But when I try to remove it I face this error.

Thanks!

Check for the space issues and also the permission on the Temp folder. If the permission is read-only then change it to Full Control.

https://social.technet.microsoft.com/Forums/en-US/bb81cf3f-de94-409e-b1aa-63803632afe4/the-temp-folder-is-on-a-drive-that-is-full-or-is-inaccessible-when-search-service-started?forum=sharepointsearchlegacy


MySQL partition by date doesn't pruning the query I have this table:

CREATE TABLE `location` (
  `id` int(11) NOT NULL,
  `lat` DOUBLE NOT NULL,
   #...
  `date` datetime NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  KEY `pk` (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (MONTH(date))
(PARTITION part0 VALUES LESS THAN (2),
 PARTITION part1 VALUES LESS THAN (3),
 PARTITION part2 VALUES LESS THAN (4),
 PARTITION part3 VALUES LESS THAN (5),
 PARTITION part4 VALUES LESS THAN (6),
 PARTITION part5 VALUES LESS THAN (7),
 PARTITION part6 VALUES LESS THAN (8),
 PARTITION part7 VALUES LESS THAN (9),
 PARTITION part8 VALUES LESS THAN (10),
 PARTITION part9 VALUES LESS THAN (11),
 PARTITION part10 VALUES LESS THAN (12),
 PARTITION part11 VALUES LESS THAN MAXVALUE);

I use partition by Month function to speed up the queries because I have a huge amount of data  in this table.

But when I tried a query like this:

Explain SELECT * FROM test.new_table where date between '2017-02-02' and '2017-04-25';

I got this result:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
1 | SIMPLE  | new_table | part0,part1,part2,part3,part4,part5,part6,part7,part8,part9,part10,part11  | ALL      |           8   | 12.50 |   Using where

I see that I can't benefit from Pruning feature of MySQL.

What I can do else to enhance incoming queries ?

Edit

The most used case to get data is Between 2 dates.

Examples of queries:

SELECT
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.`date` BETWEEN `u`.`date_start` AND `u`.`date_end`
 OR (ISNULL(`u`.`date_end`) AND (`l`.`date` >= `u`.`date_start`))

2

SELECT
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.id in (...)

BY RANGE(MONTH(date)) does not prune.

Even if id did prune, it would probably not provide any performance benefit over having a non-PARTITIONed table with a suitable index.  May we see your queries?

BY RANGE(TO_DAYS(date)) does prune.  But you need to add a new partition periodically, and (optionally) DROP an old partition.

Blog on partitioning.


BCP via Linux Centos 7, cannot connect to Azure SQL I installed sqlcmd, bcp and odbc 17 for microsoft.

then I connected to Azure SQL with SQLCMD - it worked OK

then I tried to import some data with BCP and i got an error

I use:

 bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "password" -d schema -c -t ','

SQLState = 28000, NativeError = 18456
Error = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'user_name'.
SQLState = 01S00, NativeError = 0
Warning = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

My passwords contaains ] % ; , is it could be a problem?

Update:

The issue is that when I use SQLCMD unixODBC uses {} to escape password and it works OK, but when BCP uses unixODBC password is not escaped.

SQL CMD

[ODBC][12517][1530961370.439037][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x1b56e20
            Window Hdl = (nil)
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};SERVER={example.database.windows.net};UID={user_name};PWD={password};WSID={ADCSrv...][length = 145 (SQL_NTS)]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

BCP

[ODBC][19984][1530962482.429621][SQLDriverConnect.c][726]
        Entry:
            Connection = 0xcb4ef0
            Window Hdl = (nil)
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};server=example.database.windows.net;UID=user_name;PWD=password][length = 109 (SQL_NTS)]
            Str Out = 0x7fff5397ce70
            Str Out Max = 2048
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

How to fix that? I think about creating a DNS

For Azure SQL Database, include the database name on the command line:

bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "password" -d schema -c -t ',' -d YourDatabase

EDIT:

If your password contains characters which must be escaped (e.g. ';' but not '%'), you need to enclose the password string in braces:

bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "{pass;word}" -d schema -c -t ',' -d YourDatabase


Inserting to table: Insert rows with Left Outer Join or Insert rows then Update? I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row.
Not every row in Orders will have a row in Deliveries (digital media, warranties, etc), but if an order does have a delivery, it will have only one. I am trying to combine data from these two tables into a third table (OrderInformation) to be used for reporting.

I can create the final table one of two ways:

Inserting all orders into the OrderInformation table first and then updating only the rows that have a matching Deliveries record in a second statement.

OR
Inserting all values into the table at once using a LEFT OUTER JOIN between Orders and Deliveries.

I tested this out on my own data and the 'LEFT OUTER JOIN' query consistently outperformed the 'UPDATE' query by 200%, which surprised me.

Which option would perform better in general? And why does it perform better that way? Is performance contingent on join conditions, the type of data involved, etc?

When Orders and Deliveries are both properly indexed, the second query will generally perform better. There are two main considerations here:

Generally speaking, and without seeing the details of your tables - what probably happens with the INSERT/UPDATE construct is that it will first allocate a number of pages in the database in the initial INSERT. But when you UPDATE those records, the added new information has to fit inside those existing rows in the OrderInformation table's pages, which causes page splits, which are very I/O intensive and can fragment your table and/or indexes.

The second query (single INSERT using LEFT JOIN) is considerably better practice, since it minimizes the locking requirements. Imagine if another simultaneous process creates a new row in Orders and then a related row in Deliveries between the time of the INSERT and the UPDATE statements in the first example - you'll get a foreign key constraint violation unless you've put all of this in a potentially large transaction, which itself implies a bit of overhead.


SUM Mixed With Case In Select Statement I am attempting to run this SELECT statement, but I keep getting error -

  Msg 130, Level 15, State 1, Line 9
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

This is my syntax, how should this be re-written in order to succesfully execute?  I also add here make-up DDL to better illustrate question.

Declare @info TABLE (region varchar(50), p1 float, p2 float, p3 float, p4   float, p5 float, necombs float, d1 datetime)
INSERT INTO @info (region, p1, p2, p3, p4, p5, necombs, d1) VALUES
('west', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/01/2015 08:00:00.000')
,('east', 2.11, 3.22, 3.33, 1.44, 2.11, 12.00, '01/03/2016 08:00:00.000')
,('north', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/05/2015 08:00:00.000')

Select
region
,SUM(CASE WHEN YEAR(d1) = '2015' THEN ISNULL(p1, 0)+ISNULL(p2, 0)+ISNULL(p3, 0)+ISNULL(p4, 0)+ISNULL(p5, 0)/NULLIF(SUM(ISNULL(necombs, 0)), 0) ELSE 0 END) [Answer]
FROM @info
Group By Region

The accepted answer doesn't show the 2016 row, which I would expect to be 0 based on the initial SQL in the question.

Here's an alternative that would give the additional 2016 row:

 SELECT i.region,
        DATEPART(YEAR, i.d1) AS YearNumber,
        SUM(rd.Calc) / ISNULL(SUM(ISNULL(necombs, 0)), 0) Answer
 FROM @info i
 CROSS APPLY (VALUES(CASE WHEN DATEPART(YEAR, d1) = '2015' THEN ISNULL(p1, 0) + ISNULL(p2, 0) + ISNULL(p3, 0) + ISNULL(p4, 0) + ISNULL(p5, 0) ELSE 0 END)) AS rd(Calc)
 GROUP BY Region, DATEPART(YEAR, i.d1);


Simple client query tool question Hope everyone is having a good week. I have what I believe to be a simple straightforward question about Postgres editors.

I have worked with 3 relational databases over the last 5 years (Teradata, MySQL & MSSQL), but encountered Postgres in the last month.

I'm an analyst, so my day to day is a lot of ad hoc querying.

In the past I have been used to the idea of simple luxuries: tabs in query editors, a quick CMD-S to save, etc.

My question is - why do these features lack in so many Postgres editors?

Postico for example has no easy save option - who does not save queries they write?

Is the way Postgres typically is used fundamentally different than other databases? I understand some are optimized for high throughput etc, but is my use (analyst) so very different than features developers would need?

Is it just because I'm relying on open source editors?
Anyone have recommendations?

Thanks
-Justin

My question is - why do these features lack in so many Postgres editors?

What are you missing? You can do it all with psql[1] or PGAdmin III[2].

Take a look[3][4] how to customize psql to interact with our editor/environment.

  Postico for example has no easy save option - who does not save queries they write?

That's a good question. :P

  Is the way Postgres typically is used fundamentally different than other databases? I understand some are optimized for high throughput etc, but is my use (analyst) so very different than features developers would need?

IMHO the PostgreSQL community share a ideia of make a simple tool for each job. You can do all the job, but using each of this 'tiny' tools.

Database vendors, like Microsoft and Oracle, always make a super tool with all options and probably call this small tools to help.

MSSQL it's a great database but, without the Management Studio, it's hard to work with.

  Is it just because I'm relying on open source editors? Anyone have recommendations?

There's a few comercial options, like @Lauri already suggested, take a look at the wiki[5][6] for more options.

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/app-psql.html
https://www.pgadmin.org/
http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/
http://akorotkov.github.io/blog/2016/06/09/psql-graph/
https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
https://wiki.postgresql.org/wiki/GUI_Database_Design_Tools


Why would optimiser choose Clustered Index + Sort instead of Non-Clustered Index? Given the next example:
IF OBJECT_ID('dbo.my_table') IS NOT NULL
    DROP TABLE [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
    [id]    int IDENTITY (1,1)  NOT NULL PRIMARY KEY,
    [foo]   int                 NULL,
    [bar]   int                 NULL,
    [nki]   int                 NOT NULL
);
GO

/* Insert some random data */
INSERT INTO [dbo].[my_table] (foo, bar, nki)
SELECT TOP (100000)
    ABS(CHECKSUM(NewId())) % 14,
    ABS(CHECKSUM(NewId())) % 20,
    n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM
    sys.all_objects AS s1
CROSS JOIN
    sys.all_objects AS s2
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC);
GO

If I fetch all records ordered by [nki] (Non-clustered index):
SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 493 ms

Optimiser chooses the clustered index and then applies a Sort algorithm.

Execution plan
But if I force it to use the non-clustered index:
SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 311 ms, elapsed time = 188 ms

Then it uses non-clustered index with a Key Lookup:

Execution plan
Obviously if the non-clustered index is transformed into a covering index:
CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC)
    INCLUDE (id, foo, bar);
GO

Then it uses only this index:
SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 32 ms, elapsed time = 106 ms

Execution plan

Question

Why does SQL Server use the clustered index plus a sort algorithm instead of using a non-clustered index even if the execution time is 38% faster in the latter case?

If you were to compare the number of reads required in 100,000 lookups with what’s involved in doing a sort, you might quickly get an idea about why the Query Optimizer figures that the CIX+Sort would be the best choice.

The Lookup execution ends up being quicker because the pages being read are in memory (even if you clear the cache, you have a lot of rows per page, so you’re reading the same pages over and over, but with different amounts of fragmentation or different memory pressure from other activity, this might not be the case). It really wouldn’t take all that much to have CIX+Sort go quicker, but what you’re seeing is because the cost of a read doesn’t take into consideration the relative cheapness of hitting the same pages repeatedly.


How do I run a large script with many inserts without running out of memory? Question:

I have a script with around 45 thousand insert from select statements. When I try and run it, I get an error message stating that I have run out of memory. How can I get this script to run?

Context:

Added some new data fields to make an app play nice with another app
the client uses.
Got a spreadsheet of data from the client full of
data that mapped current data items to values for these new fields.
Converted spreadsheet to insert statements.
If I only run some of the statements it works but the entire script does not.
No. There are no typos.

If there is a different way I should be loading this data feel free to chastise me and let me know.

The maximum batch size for SQL Server 2005 is 65,536 * Network Packet Size (NPS), where NPS is usually 4KB. That works out to 256 MB. That would mean that your insert statements would average 5.8 KB each. That doesn't seem right, but maybe there are extraneous spaces or something unusual in there.

My first suggestion would be to put a "GO" statement after every INSERT statement. This will break your single batch of 45,000 INSERT statements into 45,000 separate batches. This should be easier to digest. Be careful, if one of those inserts fails you may have a hard time finding the culprit. You might want to protect yourself with a transaction. You can add those statements quickly if your editor has a good search-and-replace (that will let you search on and replace return characters  like \r\n) or a macro facility.

The second suggestion is to use a Wizard to import the data straight from Excel. The wizard builds a little SSIS package for you, behind the scenes, and then runs that. It won't have this problem.


SQL Failover Clustering with Availability Groups: Single or Multiple Databases Company wants to implement AlwaysOn SQL Failover Clustering with Availability Groups, for server level and data protection.

Are arrangements below, only possible if 1 database is on the server, or could I have multiple databases below in arrangement?

Note: First picture has 4 servers,

Second picture only has 3 failover servers. Both have availability groups.

Are arrangements below, only possible if 1 database is on the server, or could I have multiple databases below in arrangement?

You can have as many databases as your hardware will support which depends on workload, server size, and other general infrastructure items such as latency and bandwidth of the connections.

I've witnessed AGs that are ~20TB in size across 4 databases, AGs that had over 1,100 databases, and all values in between. Completely depends on your workload, hardware, and what you'd like to accomplish.


SQL Server Import Job Removed Data After Job Failed I have a SQL Server Job set up to import data from a table to a table on another server, using the Import/Export data wizard.

In one instance, the job failed (with, I believe a SQL connection issue), and with that, the data from both tables were deleted. The tables on both the source and destination server were removed.

Does this make sense? If anything, why would the data be removed from the source?

The source is SQL Server 2012 and the destination is SQL Server 2008.

Take a good look at the control flow, both in your SQL Agent job, and the SSIS package it's running. I suspect there's a step in one of them that cleans out the source table after the work is finished, and this step is running regardless of whether the import succeeded or not.

Or there may be an unrelated cleanup job that cleans out that import table.


Full overlap:

SELECT *
FROM source
WHERE Start_Date >= '2019-09-11 12:00.000'
  AND End_Date <= '2019-09-11 13:15.000'

Partial overlap (not adjacent only, for adjacents replace strong unequiations > and > with safe ones >= and <=):

SELECT *
FROM source
WHERE End_Date > '2019-09-11 12:00.000'
  AND Start_Date < '2019-09-11 13:15.000'

fiddle


WHERE clause converting INT to DATETIME Looking for advice on how to match two differing column types:

I have two tables: RT-A and DA-B.

Under both tables there is a column named DeliveryDate, but the individual tables use differing column types; one time without timezone and the other numeric.

Example: in the DA-A table the DeliveryDate column reads: "02:00:00"
Example: in the RT-A table the DeliveryDate column reads, "2".

What is the correct expression in the WHERE clause of an SQL statement to join rows where they equal each other?
Plain equality obviously does not work With the current design:

"DA-A"."DeliveryHour" = "RT-A"."DeliveryHour"

This is how I have been writing the SQL, but it seems to be terrible in form:

WHERE
  "DA-A"."DeliveryHour" = '2:00:00' AND
  "RT-A"."DeliveryHour" = '2'

To join all rows that "equal" each other:

WHERE "DA-A"."DeliveryHour"
    = '0:0'::time + "RT-A"."DeliveryHour" * interval '1h'

Or

WHERE EXTRACT(hour FROM "DA-A"."DeliveryHour")::numeric
    = "RT-A"."DeliveryHour"

One or the other may be favorable to keep one side sargable when you want to use an index in a query on one or the other side.

For the case where you are only interested in  2 / 2:00:00, your explicit expressions may be the best choice, since both expressions are sargable.


How join 2 table with calculation in the relation ID I'm using SQL Server 2012 for my RDBMS I have 3 main table Tb_Template,Tb_Value, and Tb_Mapping.
I use Tb_Mapping to join Tb_Template and Tb_Value. But the relation to Tb_Value have calculation Val_Now & Val_Before.
What I try is make Value_ID in Tb_Mapping is save query like this (example for ID 3 in Tb_Mapping)
SELECT
    (SELECT Val_Now FROM Tb_Value WHERE ID = 'VAL4') +
    (SELECT Val_Now FROM Tb_Value WHERE ID = 'VAL5')

Is there another way to make get result like View_Result
Table Tb_Template
ID    Description
-----------------
1        Desc1
2        Desc2
3        Desc3
4        Desc4
5        Desc5

Table Tb_Value
ID    Val_Now    Val_Before
----------------------------
VAL1     1            0
VAL2     5            3
VAL3     1            1
VAL4     3            4
VAL5     0            2

Table Tb_Mapping
ID   Template_ID      Value_ID
-------------------------------
1       1               VAL1
2       2               VAL2
3       3             VAL4+VAL5
4       4               VAL3
5       5        VAL1+VAL2-VAL4-VAL5

View_Result:
Description   Val_Now Val_Before
----------------------------------
   Desc1          1         0
   Desc2          5         3
   Desc3          3         6
   Desc4          1         1
   Desc5          3        -3

WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) > 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) > 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) > 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) > 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) > 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle (parenthesis in mappings removed).
PS. None Tb_Value.ID value must be a prefix of another Tb_Value.ID value. If false then update CHARINDEX checking expression accordingly.

oh maybe becase the VAL i have is VAL1 - VAL50 , because CHARINDEX() make the result like that ? example VAL9+VAL10 and the result VAL1,VAL9,VAL10 ? – Fikri

WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID + '+' Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) > 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) > 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) > 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) > 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) > 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle


Should I add multiple secondary indexes to search I have a search screen on my app where the users can use up to 4 parameters to search.
I have written a stored procedure to facilitate the search.

Select
    ID,
    FirstName,
    LastName,
    CountryCd,
    State,
    Zip,
    Data1,
    Data2,
    Data3
From
    Customer
Where
    ((FirstName like @paramfname) OR (@paramfname IS NULL) )
    AND ((LastName like @paramlname) OR (@paramlname IS NULL))
    AND CountryCd = @paramcountry
    AND ((Zip = @paramzip) OR  (@paramzip IS NULL))

I have added one secondary index that includes the 4 columns FirstName, LastName, CountryCd and Zip.

CREATE NONCLUSTERED INDEX [idx_Cust_FN_Ctry]
ON [dbo].[Customer]([FirstName] ASC, [LastName] ASC, [CountryCd] ASC, [Zip] ASC)

My question: is this one index enough for efficient search ?

If the user runs a search by using only FirstName and Country, does SQL Server know
how to use the index efficiently?

Or do I need to add 4 separate secondary indexes on each of these columns?

Thanks

It really depends.  If as in your example the users do a search for just the firstname and country then the index will be used.  Let's say your user does a search by zip.  At that point SQL may do a scan on your index (generally faster than a table scan) but an index starting with ZIP would be much faster.

The problem is every index you add increases your write time.  Generally adding indexes is a balancing act between the increased write time caused by an additional index and the benefit of the index on reads.  If for example this is a table where there are tons of writes all the time you may want to be careful with your indexes.  If on the other hand it's a read only table then you can be less careful.

I recently added a very large index to a table on a query that is only being run once a day.  However that particular query is very important to my business and HAD to go faster.  So the additional cost of the index wasn't as important as the increased speed of the query.

If I had to guess from your question I would say that this search is going to be run frequently.  Given that this is your only index on the table you might add additional indexes with LastName first and another with Zip first.  Initially I wouldn't bother with an index starting with CountryCd because it isn't going to be all that selective and so probably not as useful. If on the other hand you find users querying on CountryCd alone frequently then you may have to add it later.

After your system has been running with the new indexes for a bit then you should check the sys.dm_db_index_usage_stats DMO to confirm that the index are in fact being used.


How to change a web application that now needs to become OLTP and OLAP application? Previously, I did a simple web application for an enterprise client using PHP and MySQL.

After some time, it has grown to a point where I realized after talking with people here that I need to treat it as two separate apps OLTP and OLAP.

I have tentatively decided that

the OLTP will still continue to run using the current PHP codebase and MySQL
the OLAP will use PostgreSQL
both should appear as a single app to the end user. This is to avoid inconsistencies and too many logins.
the OLAP should help with sending out scheduled reports via email

I want to avoid a "big bang" approach. At the same time, I don't want to do the OLAP as an isolated app.

I want to slowly evolve to the point where transaction processing and the analytic processing functions are modular and consistent.

How do I approach this and bearing in mind, I currently have only 1 physical server that acts as both database and application server at the moment?

I am most familiar with PHP and am okay to use Python.

The physical server is running ubuntu 14.04 and nginx as web server.

The normalized MySQL currently is at 20mb and growing at a rate of 1 mb every month or so.

EDIT

My server shows 8 when I run grep -c processor /proc/cpuinfo

Each processor (I think?) shows the following stats:

vendor_id   : GenuineIntel
cpu family  : 6
model       : 44
model name  : Intel(R) Xeon(R) CPU           L5630  @ 2.13GHz
stepping    : 2
microcode   : 0x13
cpu MHz     : 1600.000
cache size  : 12288 KB

When I type free -m

             total       used       free     shared    buffers     cached
Mem:          7972       3052       4920         46        226       2039
-/+ buffers/cache:        785       7186
Swap:            0          0          0

When I ssh in

System load:  0.28                Processes:          229
  Usage of /:   13.9% of 126.32GB   Users logged in:    1
  Memory usage: 10%
  Swap usage:   0%

OK - I'm going to slightly revise my advice in the previous thread.

If you have only 1  physical server, you're always going to have problems with OLTP conflicting with OLAP - i.e. disk bottleneck. It would be far easier to use PostgreSQL as the server for both than the other way round - see my previous comments on the richer PostgreSQL SQL functionality.

We also don't know what sort of activity your app has, nor do we know your CPU, RAM and disk config (RAID?).

In the light of what you have written above, my advice is to use PostgreSQL for both - maybe two separate schemas on the same instance of PostgreSQL - or possibly two instances of PostgreSQL on the same machine. This is more or less what @Mat and @CraigRinger said. The logic behind separating the schemas is that when and if you get a second server (which you should), parititioning the functionality between the two machines will be trivial.

If most of the functionality of your app to date is of a CRUD nature, then porting it to PostgreSQL should be relatively painless, but, again, YMMV.


Sql Server - How to convert Audit log into a table? I am using Sql Server 2014, Standard Edition. My objective is to trace the history of operations on the tables (insert, update, delete), and to display on the web so the users are able to tell what happened to the data in the past.

So, I came to know there is a partial support of Audit feature in the Sql Server Standard Edition, and was able to log some operations, as attached.

My question is, how to make those logs stored inside a proper Sql Table so that the records could be accessed, retrieved and displayed on the web page?

Currently, the Audit Destination allowed:
File, Security Log, Application Log
... which don't seem to be able to be retrieved directly from an ASP.NET API.

Is it possible to log into an SQL Table?

P.S.:

(Correct me if I am wrong). Many have said that the Standard Edition only allows Server-Level Audit, but NOT Database Level Audit. However, for some unknown reasons, if I added these 7 audit types (yup, 7 or none), I am able to capture and view the database level operations.

Capturing DML operations on individual tables is among the auditing features that are not available in Standard Edition.

If you want to trace DML operations, another possible hook is Extended Events. You won't be able to use the auditing package, but you could use lock acquired events to capture read and write operations.

I blogged about it here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

Other options are:

Triggers: this is easy to set up, even automatically by generating the trigger code. Downside is the increased write overhead.
App-side logging: If you have control over the application, you can inject the code needed to log accesses to the tables.


Net Configuration Assistant doesn't open, created Listener in Net Manager but it isn't recognized when creating database Win 7 I have installed Oracle 11g on my Windows 7 64-bit system. The Net Configuration Assistant does not open for me to create a listener. I was able to add a listener through the Net Manger and it says it is configured correctly. But when I create a database it doesn't recognize the listener so I have to uncheck "Configure Enterprise Manager" in order to complete the creation of my database.

Is there another way to access the Net Configuration Assistant other than through the start menu so that the listener will be recognized by the database?

Try to run Netca "as Administrator".

The way Net Configuration Assistant and Net Manager create listener is the same - they both create configuration file - listener.ora. The difference is - Netca starts the service, but Net Manager is not.

Also, you can check if your listener is running by looking into Control Panel - Administration - Services - something like "OracleOraDB11gHome1TNSListener".


What is shared between two instances on the same server? We want to install multiple instances of SQL Server on a single Windows server.  We cannot use VMs because the server is bare metal Windows 2008 R2.

For security purposes we need to ensure that the two instances share nothing, including management, Windows services, accounts, etc.  So I guess the question is:

When installing more than one "instance" of SQL Server on a Windows server, what is shared?

SQL Server instances have separate sets of binaries for each installation.  For the base engine, you will have separate SQL Server and SQL agent services that can run under separate accounts.  They each have their own configurations and users.  This is also true for other components like Analysis Services and Reporting Services if you install them.

There are shared components, including Management Studio, Integration Services, and the SQL Browser service.  You can find a full listing of shared components here.

Note, you will have several considerations to make when maintaining different instances:

SQL Version consistency (each instance can be of a different SQL version)
Memory Settings - Each instance will maintain a memory pool separate from the other instances.  You will want to plan out your Max Memory settings for each instance so that the different instances won't compete with each other.
Possible CPU contention - All instances will make use of the same CPU cores.  You can manage the processor affinity mask if you want to reserve threads for different instances, but I haven't had much of a need for that.

Overall, you'll need to keep in mind that all your instances will be competing for resources on the same server, so balance those resources accordingly.


Issue importing large file into SQL server I am trying to import a 4gb csv file into a new table in sql server using the wizard. And when I do I get the errors below. I also included the 2nd row of the file I am trying to import without the headers. Any idea on how I can fix this?

  Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data
  conversion for column ""Other Provider Identifier Issuer_2"" returned
  status value 4 and status text "Text was truncated or one or more
  characters had no match in the target code page.".  (SQL Server Import
  and Export Wizard)

  Error 0xc020902a: Data Flow Task 1: The "output column ""Other
  Provider Identifier Issuer_2"" (466)" failed because truncation
  occurred, and the truncation row disposition on "output column ""Other
  Provider Identifier Issuer_2"" (466)" specifies failure on truncation.
  A truncation error occurred on the specified object of the specified
  component.  (SQL Server Import and Export Wizard)

  Error 0xc0202092: Data Flow Task 1: An error occurred while processing
  file
  "C:\Users\test\Desktop\NPPES_Data_Dissemination_June_2013\npidata_20050523-20130609.csv"
  on data row 79.  (SQL Server Import and Export Wizard)

  Error 0xc0047038: Data Flow Task 1: SSIS Error Code
  DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source
  - npidata_20050523-20130609_csv" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called
  PrimeOutput(). The meaning of the failure code is defined by the
  component, but the error is fatal and the pipeline stopped executing.
  There may be error messages posted before this with more information
  about the failure.  (SQL Server Import and Export Wizard)

You're getting a truncation error, meaning the data that's coming in is larger than the field size you have specified.  Try increasing the destination table field size and you should be good to go.

I get these alot when I work with email addresses as the SQL import wizard defaults to 50, I always have to increase it every time I do a fresh import.


Slow query log of wp_options WHERE autoload = 'yes' Slow query log:

# User@Host:
# Query_time: 181.648376 Lock_time: 2.114138 Rows_sent: 1425838 Rows_examined: 1663532
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Table has only 650 rows.
Any ideas how can this happen?

It is impossible that a query like that SELECT ... FROM table WHERE column = value
examines 1663532 rows and sends to the SQL layer 1425838 when the table has only 650 rows. Worst case scenario, it would do a full table scan, examining 650 rows and sending all of them if all of them have autoload = yes.

So:

The query log is incorrect (seems to be truncated, maybe it corresponds to another query?)
The table had that number of rows at some time in the past, on a different session, or as a temporary table over the real query (it was locked for 2 seconds, so probably there is some write-like activity there).

Please check the result of that select with EXPLAIN and with its handler operations.

Also you can check on the slow log or binary log to detect some ongoing write activity at that time.


SQL Server cluster resource won't come online unless its on port 1433 The 2-node SQL Server 2008 R2 cluster works fine until one of the resources got removed. I successfully added it back. However, the cluster is not able to online the SQL Service unless it is on 1433 and not the port I was using before. Below is the application error log:

  [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 274d; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

The service status becomes Pending Online and after a while, it becomes offline. Using the SQL Configuration Manager, I revert to 1433 and it works fine again.

I have tried disabling checkpointing of the quorum (How to Change the Dynamic Port of the SQL Server Named Instance to an Static Port in a SQL Server 2005 Cluster) but nothing works unless I use an alias with the old port number.

Am I missing something here?

A default instance install (instance name of MSSQLSERVER and on port 1433) will disable the SQL Browser service because 1433 is assumed.  On a clustered SQL install the cluster service will try to connect tot he instance and run a "select @@servername" in order to show that the server is up, but because it's a default instance it tries to use 1433 by default.  I've tried to re-enable the SQL Browser service after changing the port so that the mapping of instance:port happens but haven't had any luck.

What you can try is to set up a client side alias (using SQL Server Configuration Manager) on each node with the virtual SQL name of your default install (the network name) and then put the port number in the alias so that SQLVIRTUAL points to SQLVIRTUAL,3876 or whatever port you are trying to change to.  This way when the cluster service tries to poll the instance to see if it's up on 1433 it will be redirected to the actual port by the alias.  You will not be able to use a dynamic port for this.


PostgreSQL: Query DB for rows containing integer[] > or < than specified value I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less than a specified value? Here's an example table:

+=========+==================+
| city    | temps            |
+=========+==================+
| Seattle | {65,72,63,56,72} |
+---------+------------------+
| Miami   | {83,75,69,72,79} |
+---------+------------------+

Let's say I want to query for cities which have experienced a temp higher than 80. In this example only Miami has experienced temps higher than 80, so only that row should be returned. I've tried a few queries with no success, looked into intarray, but that doesn't seem to solve my problem either. Thanks much!

I'm running PostgreSQL 9.1

Assuming your table is named readings:

Find cities where at least one temperature is bigger than 80:

select *
from readings r
where exists (select 1
              from unnest(r.temps) i
              where i > 80)

Find cities where all temperatures are bigger than 68:

select *
from readings r
where 68 < all (select i
                from unnest(r.temps) i)


Improve the insertion rate of mongo sharded cluster I can't disable the write concern while i mongorestore with .bson file. It totally damages the insertion rate (insert/sec).

This is my sharding status.

mongos> sh.status()
--- Sharding Status ---
sharding version: {
"_id" : 1,
"version" : 4,
"minCompatibleVersion" : 4,
"currentVersion" : 5,
"clusterId" : ObjectId("546bd498f0531e27d40544b2")
} shards: { "_id" : "shard2", "host" : "shard2/hadoop5.xx.com:27018" } databases: { "_id" : "admin", "partitioned" : false, "primary" : "config" } { "_id" : "deneme6", "partitioned" : false, "primary" : "shard2" } { "_id" : "seref", "partitioned" : true, "primary" : "shard2" }

This is my shard. I already changed the property of getLastErrorDefaults w:0 for disable write concern.

shard2:PRIMARY> show tables; me oplog.rs startup_log system.indexes system.replset          shard2:PRIMARY> db.me.find() { "_id" : ObjectId("546bd2869e14c01f13708886"), "host" : "hadoop5.xx.com" }
shard2:PRIMARY> db.system.replset.find() { "_id" : "shard2", "version" : 2, "members" : [ { "_id" : 0, "host" : "hadoop5.xx.com:27018" } ], "settings" : { "getLastErrorDefaults" : { "w" : 0 } } }
shard2:PRIMARY>

This is my mongorestore command which load data via query router ( mongos )

mongorestore --db seref --collection can11 --noobjcheck --w 0 --noIndexRestore --host       10.21.12.21 --port 27017 /tmp/iabc_kwd.bson

When i try to mongorestore on sharded cluster via mongos the insertion rate is 300k  byte / sec , when i try to mongorestore on single mongodb the insertion rate is 12m  /sec.

How can i speed up the insertion rate of restoring mongodb sharded cluster?

-- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 3,
    "minCompatibleVersion" : 3,
    "currentVersion" : 4,
    "clusterId" : ObjectId("546ca29960e36f8983130015")
}
  shards:
    {  "_id" : "shard0000",  "host" : "hadoop5:27018" }
    {  "_id" : "shard0001",  "host" : "hadoop6:27017" }
    {  "_id" : "shard0002",  "host" : "hadoop4:27017" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "test",  "partitioned" : false,  "primary" : "shard0000" }
    {  "_id" : "seref",  "partitioned" : true,  "primary" : "shard0000" }
    {  "_id" : "abc",  "partitioned" : false,  "primary" : "shard0001" }
    {  "_id" : "mahmut",  "partitioned" : true,  "primary" : "shard0002" }
    {  "_id" : "incee",  "partitioned" : true,  "primary" : "shard0002" }
        incee.onur
            shard key: { "_id" : "hashed" }
            chunks:
                shard0000   1
                shard0002   501
            too many chunks to print, use verbose if you want to force print

All shards computer have 8 GB RAM , and they have 1.3 TB HDD.

The problem (from what I can see)
From what I can see from your output, you have a monotonically increasing shard key like ObjectId or a Date or something similar.
MongoDB sharding is done with key ranges over the selected shard key. Put simply, it works like "shard0000 is supposed to hold the rang from -infinity to $i, shard0001 the keys from $h to $p and shard0002 from $q to infinity. With a monotonically increasing shard key, every new value will be bigger than the last one inserted, heading to infinity, so the shard which will be used is always shard0002, instead of the writes being balanced to all shards.
So all of the write operations seem to hit shard0002 with individual chunks being migrated to the other shards. The necessary chunk migrations together with added network latency and a lot of metadata updates going on, it may well be that you have a poor write performance.
The solution
Really bad news: you can't change the shard key once it is set. You basically have to create  new sharded collection with a better shard key. For choosing a proper shard key, please read Considerations for Selecting Shard Keys in the MongoDB docs.
P.S. I wasn't sure wether to answer this question, since it is slightly off topic on Stackoverflow, which is aimed at programming questions. But since usually the developers are supposed to choose the shard key, my intention was to clarify things for them. Additionally, the data modeling might be influenced by sharding considerations, so – by a small margin – I thought it was ok to answer here. However, dear reviewer, feel free to flag this answer for deletion if you think differently. I am really not sure.


Running members of a job subset at different intervals So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for each schedule type.

Is this possible?

If the jobs are all scheduled to run at the top of the hour, then you could simply have a single job, with a single step, and schedule the job to run every hour (or on select hours). Then the step can have logic like this:

DECLARE @h INT = DATEPART(HOUR, CURRENT_TIMESTAMP);

IF @h = 1
BEGIN
  EXEC dbo.ProcedureToRunAt1AM;
END

IF @h = 2
BEGIN
  EXEC dbo.OneProcedureToRunAt2AM;
  EXEC dbo.AnotherProcedureToRunAt2AM;
END

IF @h IN (4,16)
BEGIN
  EXEC dbo.OneProcedureToRunAt4AMand4PM;
END

If you need it to be more granular, you can check the minute, minute/10, etc.


Comparison between A non-clustered primary key and a covering index in terms of performance I have a table which contains a non-clustered primary key. I intend to remove this primary key and create a unique Covering Index on the same column instead.
Then The table won't have a primary key, but a unique covering index.
I searched google but could not find a relevant topic. Is that fundamentally wrong? or it's ok to be done.
Update:
Why do I need to convert this index to a covering index?
Because this is the primary key and I have many select queries based on this key. At the moment for each select, there will be a need for a RID Lookup because this is a non-clustered key. If I convert it to a covering index, this RID Lookup will be gone for those select queries and hopefully there will be better performance (at least the excecution plan says that).
Of course integrity is also intended and that's why I will make it a unique covering index. The primary key is a uniqueidentifier, generated by the application (which explains why we don't make it clustered)..
What I am wondering is if that has any negative effects on anything else.
Execution Plan Before:

Execution Plan After:

FYI, the primary-key is a uniquidentifier which is generated in the code (application).  In load tests we came up with the conclusion of making it non-clustered.

By removing the primary key and adding a unique covering index with more columns, you are fundamentally changing how the table accepts data.  It may be that with the covering index in place, the table design no longer fits the purpose it was designed for.

Take for instance:

USE tempdb;

IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL
DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
    ID1 int NOT NULL
    , ID2 int NOT NULL
    , SomeDate datetime2(7) NOT NULL
    , CONSTRAINT PK_T1
        PRIMARY KEY NONCLUSTERED
        (ID1, ID2)
);

INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

The output:

  (1 row(s) affected)
  Msg 2627, Level 14, State 1, Line 18
  Violation of PRIMARY KEY constraint 'PK_T1'. Cannot insert duplicate key in object 'dbo.T1'. The duplicate key value is (1, 1).
  The statement has been terminated.

The table design is only allowing rows where the tuple (ID1, ID2) is unique.

Then look at this, where the primary key does not exist, and has been replaced with a wider unique covering index:

IF OBJECT_ID(N'dbo.T2', N'U') IS NOT NULL
DROP TABLE dbo.T2;
CREATE TABLE dbo.T2
(
    ID1 int NOT NULL
    , ID2 int NOT NULL
    , SomeDate datetime NOT NULL
);

CREATE UNIQUE INDEX IX_T2
ON dbo.T2 (ID1, ID2, SomeDate);

INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

  (1 row(s) affected)

  (1 row(s) affected)

Table T2 has both rows, where arguably, the second insert should have failed and the table should only have a single row.  In this design, the table is now accepting rows where the tuple (ID1, ID2) are not necessarily unique.

I'd think carefully about your table design, and potentially just add the covering index without removing the primary key.


Which characters are considered "word" characters Fulltext searchin with InnoDB in MySQL only includes certain characters referred to in the docs as "word characters" and mentioned here:

  For the built-in full-text parser, you can change the set of characters that are considered word characters in several ways, as described in the following...

My question is, for a given character set, how can I determine which characters are considered "word" characters?  My table is utf_general_ci. I did a bit of trial and error and found for instance, that "¬" is not treated as a word character but "Ω" is. I'm looking for either a clearly defined reference or a tool of some support so that I can find this out without doing trial and error.

The characters considered for forming a word, is explained in the Documentation:

  The MySQL FULLTEXT implementation regards any sequence of true word
  characters (letters, digits, and underscores) as a word. That sequence
  may also contain apostrophes ('), but not more than one in a row. This means that
  aaa'bbb is regarded as one word, but aaa''bbb is regarded as two
  words. Apostrophes at the beginning or the end of a word are stripped
  by the FULLTEXT parser; 'aaa'bbb' would be parsed as aaa'bbb.

Regarding Delimiters:

  The built-in FULLTEXT parser determines where words start and end by
  looking for certain delimiter characters; for example,   (space), ,
  (comma), and . (period).

Besides the above, there are Storage engine specific limits as well, on the minimum word size:

  Any word that is too short is ignored. The default minimum length of
  words that are found by full-text searches is three characters for
  InnoDB search indexes, or four characters for MyISAM.

Now, regarding the list of defined "True Word Characters", MySQL has given flexibilities, to be able to add/remove additional characters, for word criteria. As stated in the Documentation:

  Suppose that you want to treat the hyphen character ('-') as a word
  character. Use one of these methods:

  Modify the MySQL source: In storage/innobase/handler/ha_innodb.cc (for InnoDB), or in storage/myisam/ftdefs.h (for MyISAM), see the
  true_word_char() and misc_word_char() macros. Add '-' to one of those
  macros and recompile MySQL.
  Modify a character set file: This requires no recompilation. The true_word_char() macro uses a “character type” table to distinguish
  letters and numbers from other characters. . You can edit the contents
  of the  array in one of the character set XML files to
  specify that '-' is a “letter.” Then use the given character set for
  your FULLTEXT indexes.

Now looking at the source code ha_innodb.cc here:

#define true_word_char(c, ch) ((c) & (_MY_U | _MY_L | _MY_NMR) || (ch) == '_')

For some simple character-sets like latin1, one can edit the <ctype><map> array in the respective .xml file: https://github.com/mysql/mysql-server/blob/5.7/sql/share/charsets/latin1.xml#L25

However, utf8 being a complex character-set, it is implemented in the ctype-utf8.c file . It contains the complete character set definitions. Now,  array elements are bit values. Each element describes the attributes of a single character in the character set. Each attribute is associated with a bitmask, as defined in include/m_ctype.h file:

#define _MY_U   01  /* Upper case */
#define _MY_L   02  /* Lower case */
#define _MY_NMR 04  /* Numeral (digit) */

So, the characters having attributes associated with above bitmasks will be considered as true word character. Check out some explanation here --> https://dev.mysql.com/doc/refman/5.7/en/character-arrays.html

Finally, there is a list of stopwords (InnoDB has limited list, but MyISAM has quite a big list), which are ignored. Eg: about, are, com etc.

  Words in the stopword list are ignored. A stopword is a word such as
  “the” or “some” that is so common that it is considered to have zero
  semantic value. There is a built-in stopword list, but it can be
  overridden by a user-defined list.


Can not reset MariaDB password "can't connect to local MySQL server through socket" When I go

mysqld_safe --skip-grant-tables &

This message is outputted.

170802 14:14:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

When I try to run mariadb, I get

$ mariadb
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

When I check the logs, I don't see anything.

This is because mysqld_safe silently fails if it doesn't have permissions and it can't write to the log as in the event of being run as a regular user.

Instead run,

sudo mysqld_safe --skip-grant-tables &


Upgrading Mongodb from 3.2.6 to 4.2. Which is the better way to do it? What is the best way to upgrade MongoDB?

Say I have 3.2.6 and I want to upgrade to 4.2

Can I just use mongodump and mongorestore? Or are there any intricacies to it?

I would follow one of these documents that are published by MongoDB.

Upgrade a Replica Set to 4.2
Upgrade a Standalone to 4.2


Does remove document automatically remove the index that reference this document? I am wondering if removing the document will automatically update the index if this document is referenced?

Delete just put a flag on the document that is deleted. I am not sure but i think it doesn't reorganize the indexes at the same time, if that is your question, because reorganize the b-tree is an expensive operation.


Restore database into new DB without replacing the file structure Our Network Admin has setup a MS SQL 2012 server and wants the DBs setup so the database files are located on 4 different drives.

My problem here is when I try to restore a DB from a different system that only uses a single database file, it wants to bring the old file structure over with it.  In the restore options, I can see the file structure it wants to use (1 database file), but I cannot change it.

Is there a way to restore the database into a new database without losing the file structure?

Thanks in advance.
Richard

If your current database is made up of one data file and one log file then that is how it will be restored.

What purpose does the network admin in doing this if the current database is one single data file already? Does network admin understand how file structure works in a database?

If you just wanted to split the tables and indexes between multiple files you would need to manually create each in their own file group, assigned to its own physical file. Then you could import the data into those tables. The other way is to do that same thing after you restore it.

Outside of that if you involve partitioning tables between files it becomes more cumbersome and involved.


Mysql - Each mysql database with its own user? I developed a web app in which each company that registers on the app gets its own database with its own mysql-user credentials. Is this approach fine OR can I use one master mysql-user account to connect to and administer all the databases of various companies in my web app? The companies don't care how I have implemented the backend.

I would opt for different users accounts for each database.

If you implement the logon module seperately from the rest of the application, you have something that looks more like a portal to individual provisioned environments. That leaves you with more options in the future.

The only drawback I can think of is that this could lead to many database connections, not sure when that is a performance killer.

Having one connection to the database (and thus one session), means you have to switch between databases while handling requests from different companies. You should investigate how this affects performance as well.


Shedding database server load from a web application: how to measure? I work on a dotnet / WebForms web application that's load balanced over a number of web servers. The web servers connect to a SQL Server 2016 instance.

This web application includes several ways for end-users to request reports. Some of those reports take a lot of resources. I'm hoping to add a feature to the web app that says "Sorry, we're too busy to honor this request now. Please try again later."

Is there a good in-band way -- a good query or SP -- to ask SQL server how loaded it is at the moment? It happens that most overloads in our environment involve high cpu utilization.

If there's no good query, can anyone suggest an out-of-band way to do this reliably and simply? Maybe some sort of WMI operation.

Once I know how loaded the DBMS machine is, then I can decide whether to ask my end-users to come back later.

Edit. Two reasons to do this rather than add a server.

the user-frustration factor. If a request takes too long, the user may figure something's wrong and request it again. The way things work in web apps, SQL queries keep going even when the user has abandoned the request.
more servers cost money. The business I'm serving is self-funded. Managing peak load is as good a strategy as adding 24x7 base load capacity.

In my view the challenge will be to define what is loaded.  Unless you have a baseline tied to business hour (with full cycle, day/night, weekday/weekend etc) there is no way to determine if you are overloaded.  I noticed you mentioned:

  most overloads in our environment involve high cpu utilization

If you want to base you alert on one data point (high cpu) you can use this query to see what percent of cpu is being consumed at this moment.  You can change the top(10) value and get back to 256 minutes.

I copied this query from here.

DECLARE @ts_now BIGINT = (SELECT cpu_ticks / ( cpu_ticks / ms_ticks )
   FROM   sys.dm_os_sys_info);

SELECT TOP(10) sqlprocessutilization AS [SQL Server Process CPU Utilization],
              systemidle             AS [System Idle Process],
              100 - systemidle - sqlprocessutilization AS [Other Process CPU Utilization],
              Dateadd(ms, -1 * ( @ts_now - [timestamp] ), Getdate()) AS [Event Time]
FROM   (SELECT record.value('(./Record/@id)[1]', 'int')
                      AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
            [timestamp]
 FROM   (SELECT [timestamp],
                CONVERT(XML, record) AS [record]
         FROM   sys.dm_os_ring_buffers
         WHERE  ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                AND record LIKE '%<SystemHealth>%') AS x) AS y
ORDER  BY record_id DESC;

With this approach you have no way to check if SQL Server (beside kernel process) is consuming all of the cpu.  Hopefully in your case you do not have other applications installed in the same host.

Adding few PowerShell commands.

Get-WmiObject win32_processor | Measure-Object -property LoadPercentage -Average | Select Average

Get-Counter '\Processor(_Total)\% Processor Time'

I can make this answer to a question how to detect high cpu consuming sessions and try to fix those but assuming that is not what you are looking for.  I hope you already know what is causing high cpu and at this moment you cannot do anything about that.

I want to point out high cpu is not a bad thing unless your ‘runnable’ tasks are waiting for scheduler to start working.  CPU is expensive and we pay lot of money to license those, why not let our money work to the full capacity?


Multiple Instances in AlwaysOn Availability Groups - Resource Balancing I have 2 SQL 2017 Instances on each node in a 2 node cluster with a file share witness. 8 cores and 64GB RAM on each.
Node A acts as the primary for Instance 1 and Node B acts as the primary for Instance 2 . (Synchronous)
Node B acts as the secondary for Instance 1 and Node A acts as the secondary for instance 2. (Synchronous)
I am wondering what the right balance of resources (Max DOP and Max Memory) would be in the ideal scenario? Would I be shooting myself in the foot by setting maxdop = 8 on the primaries? What is advisable for Maxdop on the secondaries in this situation?
Likewise, generally how much max memory should I set for the primary/secondary? (Obviously leave some for OS as well)
I am already working on a solution to re-balance the resources in the event of a failover, so no need to account for that in your reply. Unfortunately, this is for a Production build where I am unable to replicate the amount of typical load, so any generalities are welcome.
Thanks!!

I am already working on a solution to re-balance the resources in the event of a failover, so no need to account for that in your reply.

That's actually the main thing that drives the reply.  If you don't sharply limit each primary instance it will acquire an unfair share of the server memory, and you may have severely degraded performance of the other instance after failover.
If you rebalance by setting max server memory for both instances in the case of a failover, you may run without limiting it on each node normally.
I would not limit the CPU affinity or maxdop on either instance.  The OS allocates CPU resources very dynamically and fairly, so unless you observe one isntance being starved by excessive CPU utilization from the other instance, just leave those at the default settings.


View SQL Code Behind Flat File Import Wizard? Is there a way to view and copy the code that the "Import Flat File to SQL" Wizard ultimately uses, after I have gone through all the steps (chosen file, set up data types for my columns, etc.)?
While using the Import Flat File to SQL wizard, I found it cumbersome to have to select a data type for each column individually. My data had a large amount of columns to change. In the future, I would prefer doing this via SQL code to save time. I'm learning Microsoft SQL Server Studio and currently only know how to use the Import Flat File Wizard, so I would need to learn all the steps for doing it another way.
Version: 18.7.1

The "Import Flat File to SQL Wizard" is actually just an abbreviated version of the more complex "Import & Export Wizard." It uses the exact same mechanism, but streamlines the process by skipping unnecessary options & picking appropriate defaults for you. Ultimately, the import process uses the same mechanism though.
One of the "hidden" options that is only available in the Import & Export Wizard, is to generate an SSIS Package, rather than executing immediately. The import actually happens via SSIS as a mechanism, rather than pure T-SQL or PowerShell code. Because it is not executing T-SQL or PowerShell directly, it can only output the thing it generates & runs behind the scenes--an SSIS Package.
To access the Import & Export Wizard, right-click on a database, and select `Tasks-->Import Data..." (this is right below "Import Flat File..." on the context menu). Here, you can choose a flat file source, and SQL Server destination.
On the last page of the Import & Export Wizard, you can select to Save as an SSIS Package, or run immediately. If you save as an SSIS package, you will be able to see exactly what SQL Server is doing.


How can I synchronize data between two RDS MySQL databases I have 2 Amazon RDS MySQL instances, one is MySql v5.5 and the new one is 5.6.
I want to upgrade the 5.5 to the 5.6 but you cannot do upgrades at this time, so we are trying to migrate the data. I did a dump of the 5.5 and have imported the dump into the 5.6 but since I need to keep our down time to a minimum, I decided to try to do a data synchronization between the two.

What are my options for this?

I have a few tables that are huge! (1-8gb)
Tables are all innodb
Dump download took 3 hours
Dump Upload took 4+ hours

I need to try to stay under 5 hours total!

RDS for MySQL 5.6 supports "in" and "out" migration strategies of live workloads using MySQL's native replication, but RDS for MySQL 5.5, they only support "in" migration... which is unfortunate, because essentially every possible combination except the one that you need is available.  That would have been nice. :(

If your dump download took 3 hours and your upload took 4+ hours, the good news is, that you only need the 4+ hours, because you can do them simultaneously... and depending on whether you did that testing from EC2 or from your own network, possibly faster.

What I suggest is tying mysqldump to mysql using a pipeline, so that you are simultaneously reading from the old server and writing to the new server.  To make this work properly, though, we need to add a couple of twists.  And then a couple of extra twists make it almost fun.

I would think your ideal scenario would be to spin up a disposable linux machine in EC2 in the same region, of you don't already have one available... and preferably the same availability zone as your servers, because this is going to give you the maximum potential bandwidth among the 3 components involved and that's one less thing to slow you down.

Next, on the source server where the data is coming from, you need to issue SET GLOBAL NET_WRITE_TIMEOUT = 600;.  Of course, you can hardly SET GLOBAL anything on RDS, so you have to change this via the RDS Parameter Group.  NET_WRITE_TIMEOUT is the amount of time MySQL Server will wait before aborting an attempt to write to a client that is blocking.  When you're piping the output from a dump directly into another server, the destination server is going to periodically block, and sometimes for longer than the default 60 seconds.  There's a companion variable, NET_READ_TIMEOUT, that you might want to set to a similar value, although I don't think this variable has any relevance in a dump environment.  This will prevent the source server from timing out when the destination server takes too long to do one of the massive inserts that mysqldump builds.  If you have MyISAM tables, you need to set it even higher, because the ALTER TABLE ... ENABLE KEYS can take forever to run after a table is fully restored on the target... but hopefully, you don't.

The value "600" (10 minutes) is somewhat arbitary, but it's what I use for operations like this.

As mysqldump is reading from a remote server and writing to a pipe into the mysql command line client, when mysql sends a big insert to the destination, it stops reading from the pipe, which blocks mysqldump from writing to the pipe, which in turn blocks mysqldump from reading from the socket until the destination server is ready for more data.  In addition to the timeouts this causes, it also wastes a lot of time for several different reasons... mysqldump spends a lot of time discovering table structures and starting its SELECT * queries and we want to avoid all of that wasted time.  The ticket to this is setting up a double buffer using the unix dd utility.

If we invoke dd specifying a large output block size without specifying an input block size, it will read from its input with a small block size (default 512 bytes) until it has read the amount of data specified by its output block size, then block its input and flush that data out its output in a single massive write.

dd obs=16384K

This will cause dd to accept 16 MB of data as fast as you can feed it, and then, once it can flush that block to its output, it will then accept more.

If we stack two of them, we get a double buffer. :)

mysqldump [options] | dd obs=16384K | dd obs=16384K | mysql [options]

Using this construct, we can always keep between 16 and 32 megabytes of data in RAM, in flight, at all times, between the origin server and the destination server... so we are essentially guaranteed that as soon as the destination server is ready for data, we have data immediately ready for it, with no waiting for the origin... and the origin can keep itself busy keeping our buffers full.

16 MB isn't really a large mount of data, so even a larger buffer might be desirable, but we don't want to make these too big, because when they're both full, the origin has to wait until the destination can read the entire 16 MB from the 2nd buffer before the 1st buffer can flush into the 2nd buffer which causes the 1st buffer to unblock and start accepting more data from the origin... However, we can keep more data in flight if desired by chaining even more copies of dd.  They will use 100% of the specified memory but they use very little CPU.  I usually chain at least 4 of them.

Incidentally, this same hack will also get you a faster mysqldump-pipe-to-gzip (bzip, xz, etc.) if you stick some dd in between there, because it significantly increases the likelihood that you'll always have data available for your compression program to crunch on, keeping the processor utilization of the compression program maxed, which means a faster completion because the crunching gets done in less wall-clock time due to full processor utilization.

You should also probably use the --compress option on both mysqldump and mysql so that the data is compressed over the wire in both directions.  Compression isn't free, but this compress is just zlib inflate/deflate so it's not a very significant expense in CPU itself.  The slowest part of an operation like this, as you noted, is on the destination, and all the destination has to do is the less-costly decompression.

Add the --verbose option to mysqldump so that you can watch the data flowing.

And, for a little extra sweetness, a couple of throughput meters:

$ sudo apt-get install pv

This will allow you to keep an eye on the proceedings with output on your terminal resembling this example:

 inbound:  128MB 0:07:02 [    0B/s] [ 364kB/s]
outbound:   96MB 0:06:00 [ 154kB/s] [ 334kB/s]

If you know approximately how big your dump is, you can use additional options to give pv a hint of that size and it will calculate progress in percent.

Your final command looks like this (as a single line, formatted here as multiple lines for clarity):

mysqldump --compress --verbose [other options] |
pv -pterabc -N inbound |
dd obs=16384K |
dd obs=16384K |
dd obs=16384K |
dd obs=16384K |
pv -pterabc -N outbound |
mysql --compress [other options]

The [other options] of course are the usual authentication credentials and other options you used with mysqldump and with the mysql client when you did your previous restore.

If you have multiple schemas or want to otherwise go to the trouble of running parallel copies of mysqldump | mysql it might be worth a shot to decreasing your total time, as well... restoration time is a product of CPU and IO capacity on the destination server, and with RDS it seems like you might see the combined throughput of two parallel threads be faster than 1 thread.

I think RDS also has an official, supported option to temporarily disable binary logging while restoring a dump file.  You might explore that as well, as it might improve restoration time... so long as you turn it back on when you're done.


SQL Server 2019 install - Python and R failing I'm installing SQL 2019 Enterprise on the first node of a failover cluster.  Everything successfully installs except Python and R.  Error messages are as follows:
  Feature:                       Python
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:                Machine Learning Services and Language Extensions
  Component error code:          -2147467261
  Error description:             Value cannot be null.  Parameter name: accountName
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4013.40&EvtType=0xB05283BE%400x11428655&EvtType=0xB05283BE%400x11428655

  Feature:                       R
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:                Machine Learning Services and Language Extensions
  Component error code:          -2147467261
  Error description:             Value cannot be null.  Parameter name: accountName
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4013.40&EvtType=0xB05283BE%400x11428655&EvtType=0xB05283BE%400x11428655

Haven't found anything online about that error message with accountName specifically.  Of course the link provided in the error message redirects to generic Microsoft page.
I believe Python and R are the features that download the extra cab files during setup, and I usually see a "downloading file xxxxx.cab (8XX MB)" during installation for a while, but I'm not seeing it this time.  Not sure if that's related.
Windows firewall is turned off, and Anti-Virus is disabled (I think, may need to look more into this, but nothing in the AV logs about something getting blocked).
EDIT: The server does have internet access.
EDIT2: According to THIS link, machine learning services were not supported on 2017 failover clusters, but they are supported on 2019+ failover clusters.
Any ideas?

Found the issue.  Apparently the null accountName in the error message was the account name for SQL Launchpad, which was blank during the install for some reason.  I repeated the install and gave SQL Launchpad an account name of NT Service\MSSQLLaunchpad, and everything installed correctly.