SQL Server : Is it necessary to disable SA user for security issues? We have a website that will published in national grade with millions of users with Microsoft Sql Server database.

I need to know is it necessary to disable SA login for security issues like DOS attacks ?

Thank you and sorry if question have problems <3.

Renaming or disabling sa account is just part of the story.

There is no drawback if you disable sa account.

It means if you have databases whose owners are sa, there isn't a problem.

System databases like master, or tempDb require that sa be the database owner.

If you try to change the owner of system databases you will receive 15109 error.

Similar situation is with SQL Server Agent jobs, owned by sa. They won't fail, either.

IMO good practice is to have at least one account enabled with sysadmin role.


Does this make my XtraDB Cluster hang? After seeing the following in the error log on the second node:

121003  7:16:06 [Note] WSREP: Member 0 (joiner) synced with group.
121003  7:16:06 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
121003  7:16:06 [Note] WSREP: Synchronized with group, ready for connections
121003  7:16:06 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
121003  7:17:08 [Note] WSREP: Skipping empty log_xid: COMMIT
121003  7:17:08 [Note] WSREP: ignoring DDL failure: 0 ALTER TABLE bigdata_queue_campaigns DISABLE KEYS
121003  7:17:08 [Note] WSREP: Skipping empty log_xid: COMMIT
121003  7:17:08 [Note] WSREP: ignoring DDL failure: 0 ALTER TABLE bigdata_queue_campaigns ENABLE KEYS

...

121003  7:31:33 [ERROR] Slave SQL: Error 'Table 'reportingdb.norep_zonebannertmp_bk' doesn't exist' on query. Default database: 'reportingdb'. Query: 'TRUNCATE TABLE norep_zonebannertmp_bk', Error_code: 1146

121003  7:31:33 [Warning] WSREP: RBR event 1 Query apply warning: 1, 1141

121003  7:31:33 [Warning] WSREP: Ignoring error for TO isolated action: source: 84dcb35c-0ce4-11e2-0800-4568aec9a7f3 version: 2 local: 0 state: APPLYING flags: 65 conn_id: 1106 trx_id: -1 seqnos (l: 1196, g: 1141, s: 1140, d: 1140, ts: 1349224295344525000)

I cannot login to the first node mysql -u root -p hangs. I don't see any interesting in the error log on this node.

I'm using Percona-XtraDB-Cluster-server-5.5.27-23.6.356.rhel5.

Let me know if you need further information.

If the table reportingdb.norep_zonebannertmp_bk does not exist on one of the nodes in the Cluster, this could be the source of your problem. Why? TRUNCATE TABLE is DDL. That will close the current transaction PXC has during WriteSet Operations. This cannot be rolled back. I would expect a PXC communication problem at COMMIT time because TRUNCATE TABLE causes implicit COMMIT (See my StackOverflow post from May 12, 2011). If the table reportingdb.norep_zonebannertmp_bk had existed in every node in the Cluster, then the TRUNCATE TABLE would execute without an error. You should check for any BugReports about this. You should probably submit this as a question to Percona.
There are two methods you may want to try to clear this up:
METHOD #1
You could recreate the table on the PXC (Percona XtraDB Cluster) that has the table missing

Step01) Do SHOW CREATE TABLE reportingdb.norep_zonebannertmp_bk\G on Node that still has table
Step 02) Copy and Paste that Query into /root/Fix.sql
Step 03) Change CREATE TABLE to CREATE TABLE IF NOT EXISTS in /root/Fix.sql
Step 04) Execute /root/Fix.sql on the PXC node that does not have the table

METHOD #2
You may have to perform a full SST in the event other tables are out of sync. The most aggressive way to do this is the following:
cd /var/lib/mysql
service mysql stop
rm -f /var/lib/mysql/galera.cache /var/lib/mysql/grastate.dat
service mysql start

Since there is no /var/lib/mysql/galera.cache, IST cannot be done. SST would be initiated.


Appending data from one program to the other using batch commands I have a database which sometimes crashes. I want to create a batch program that does this:

Runs SQL*Plus.
Makes SQL*Plus run a script from a plain text file.
Gets the results back to the main batch program.
Uses it to check if database is running or not.

I am having trouble in getting SQL*Plus to run something directly through the batch and getting the data back. Hopefully it's a simple process I don't know about.

Here's an example of how you can do this in shell script (bash or ksh for instance). Here's simple SQL script to test, it turns off (most?) of the usually undesired output from SQL*Plus:

set feedback off
set echo off
set heading off
set pages 0
whenever sqlerror exit 4
whenever oserror exit 8
select status from v$instance;
exit

You can run it like this, assuming your environment is set up properly (-s suppresses the banners and prompts):

result=$(sqlplus -s user/pass @script.sql)
if [ $? -eq 0 ] ; then
  # the script worked
  echo "$result"
else
  # something went wrong
  ...
fi

If you want to process multiple lines, you can use a while/read loop, with process substitution. Something like:

while read -r line ; do
   echo "Got: $line"
done < <(sqlplus -s user/pass @script.sql)

Or you can just redirect the output of SQL*Plus to a text file and process that later if it's not convenient in a variable.

On Windows, with cmd.exe scripts, it's all a bit harder, but you can use variants of what is described in this question: Setting a variable from an executable. Something like (not tested):

for /f "tokens=*" %%a in ('sqlplus -s user/pass @script.sql') do set RESULT=%%a

or

for /f "tokens=*" %%a in ('sqlplus -s user/pass @script.sql') do (
  @rem process one line at a time
  echo "%%a"
)

But you might be better off using PowerShell these days. (Which I know nothing about, but a quick google seems to indicate that there are good options for this.)


MySQL Query with good execution plan is very slow due to Sending data operation I have the following query:

SELECT
    a.borrowerId AS borrower_id,
    a.created,
    NULL AS initiator_team_id,
    NULL AS initiator_user_id,
    NULL AS old_team_id,
    NULL AS old_user_id,
    n.teamId AS new_team_id,
    a.lenderUserId AS new_user_id
FROM a
INNER JOIN b ON a.borrowerId = b.id
INNER JOIN n
    ON a.lenderUserId = n.lenderUserId
    AND a.created >= n.created
    AND (a.created < n.deleted OR (n.deleted IS NULL AND n.status = 'active'))
LEFT OUTER JOIN ass ON a.borrowerId = ass.borrower_id AND a.created = ass.created
WHERE a.`type` = 'created'
AND a.deleted IS NULL
AND ass.borrower_id IS NULL;

EXPLAIN presents me with the following plan which looks fine:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  b   ALL NULL    NULL    NULL    NULL    129132
1   SIMPLE  a   ref type,created,deleted,fk-lenderUser-activities,fk-borrower-activities,idx_type   fk-borrower-activities  4   bi.b.id 3   Using where
1   SIMPLE  n   ref unq_active  unq_active  4   a.lenderUserId  1   Using where
1   SIMPLE  ass eq_ref  PRIMARY PRIMARY 8   a.borrowerId,a.created  1   Using where; Using index; Not exists

But when I run the query it takes ~40 seconds. So I ran the MySQL Profiler and came back with this:

Status  Duration
starting    0.000009
Waiting for query cache lock    0.000004
Waiting on query cache mutex    0.000004
checking query cache for query  0.000063
checking permissions    0.000005
checking permissions    0.000004
checking permissions    0.000004
checking permissions    0.000005
Opening tables  0.000030
System lock 0.000012
init    0.000032
optimizing  0.000019
statistics  0.001302
preparing   0.000023
executing   0.000004
Sending data    40.217320
end 0.000008
query end   0.000006
closing tables  0.000011
freeing items   0.000393
logging slow query  0.000008
logging slow query  0.000005
cleaning up 0.000010

The query actually returns no data with the LEFT JOIN and NULL check present. But removing that check has no effect on the way the query actually executes.

So my question is:

Why is MySQL Sending data when the LEFT JOIN and NULL check prevent there being any rows that match?
How can I rewrite this so that it will actually perform faster?

Saying "Sending data" merely points out how useless the Profiler often is.

This index on a might change the EXPLAIN (eg, start with a instead of b) and speed up things:

INDEX(`type`, `deleted`)  -- in either order

If you need further discussion, please provide SHOW CREATE TABLE and some clues of about how big each table is.

Why is b used?  Would you get the 'right' answer if you left out

INNER JOIN b ON a.borrowerId = b.id


Query to list "last backup time" listing wrong times ( SQL Server 2008 R2 ) I use this query to list last backup dates of our databases:

SELECT SDB.NAME AS DATABASENAME,
COALESCE(CONVERT(VARCHAR(12), MAX(BUS.BACKUP_FINISH_DATE), 101),'-') AS LASTBACKUPTIME
FROM SYS.SYSDATABASES SDB
LEFT OUTER JOIN MSDB.DBO.BACKUPSET BUS ON BUS.DATABASE_NAME=SDB.NAME
GROUP BY SDB.NAME
    ORDER BY 2 ASC

But today, it shows me "09/15/2015" for all databases, but we had a problem in the cluster, so the backup job didn't run. Why is this query listing "09/15/2015' if there are no backups for today?

last time the job run was 09/14/2015.

Edit 1:

With James Anderson's query, I got this too ( filtering with bckset.[type] ='D)

Again, I don't have backups from 2015-09-15, only 2015-09-14.

EDIT 2:

With some tests, I see that these queries shows me last backup date, even if I only have log backups. Is there a way to list "last full backup" date??

Your problem may be that the backup set was created but the backup did not complete. I use the query below to check my backups

USE MSDB;
GO

SELECT TOP  300
            bckset.database_name AS DatabaseName,
            bckmdiaset.physical_device_name AS BackupLocation,
            CASE
                WHEN bckset.backup_size <= '10485760' THEN
                    CAST(CAST(bckset.backup_size/1024 AS INT) AS VARCHAR(14)) + ' ' + 'KB'
                ELSE
                    CASE
                        WHEN bckset.backup_size <= '1048576000' THEN
                            CAST(CAST(bckset.backup_size/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'MB'
                        ELSE
                            CAST(CAST(bckset.backup_size/1024/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'GB'
                    END
            END backupSize,
            CAST (bckset.backup_start_date AS smalldatetime) AS StartTime,
            CAST (bckset.backup_finish_date AS smalldatetime)FinishTime,
            CASE
                WHEN CAST(DATEDIFF(second, bckset.backup_start_date, bckset.backup_finish_date )AS VARCHAR (4)) <= 60 THEN
                    CAST(DATEDIFF(second, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Seconds'
                ELSE
                    CAST(DATEDIFF(minute, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Minutes'
            END AS TimeTaken,
            CAST(bckset.first_lsn AS VARCHAR(25)) AS FirstLogSequenceNumber,
            CAST(bckset.last_lsn AS VARCHAR(25)) AS LastLogSequenceNumber,
            CASE bckset.[type]
                WHEN 'D' THEN 'Full'
                WHEN 'I' THEN 'Differential database'
                WHEN 'L' THEN 'Transaction Log'
                WHEN 'F' THEN 'File or filegroup'
                WHEN 'G' THEN 'Differential file'
                WHEN 'P' THEN 'Partial'
                WHEN 'Q' THEN 'Differential partial'
            END AS BackupType,
            bckset.server_name As ServerName,
            bckset.recovery_model As RecoveryModel,
            CASE bckset.is_snapshot
                WHEN '0' THEN 'FALSE'
                WHEN '1' THEN 'TRUE'
            END AS IsSnapshot,
            CASE [compatibility_level]
                WHEN 60 THEN 'SQL Server 6.0'
                WHEN 65 THEN 'SQL Server 6.5'
                WHEN 70 THEN 'SQL Server 7.0'
                WHEN 80 THEN 'SQL Server 2000'
                WHEN 90 THEN 'SQL Server 2005'
                WHEN 100 THEN 'SQL Server 2008'
                WHEN 110 THEN 'SQL Server 2012'
                WHEN 120 THEN 'SQL Server 2014'
            END AS CompatibilityLevel

FROM        msdb.dbo.backupset bckset
INNER JOIN  msdb.dbo.backupmediafamily bckmdiaset ON bckset.media_set_id = bckmdiaset.media_set_id

---WHERE bckset.database_name = ''

ORDER BY    backup_start_date DESC, backup_finish_date


Transaction Log file of the secondary database in Log Shipping grows fast and fills up disk space I have a log shipping setup for DR. The transaction log file of one of the secondary databases, which is in restoring mode, is 7 times larger than the data file and is filling up the disk space. What are the possible causes and fixes?

Note that it is the transaction log file of the secondary database in the DR site that is growing fast.

For your information, for the corresponding primary database, I take a Full backup every night and transaction log backup every 30 minutes. The transaction logs are shipped every 1 hour to the secondary database.

Thanks

I have a log shipping setup for DR. The transaction log file of one of
  the secondary databases, which is in restoring mode, is 7 times larger
  than the data file and is filling up the disk space. What are the
  possible causes and fixes?

Possible causes can be a heavy transaction or bulk load ran on the primary database which causes its log file to grow and since these log files are shipped to secondary you see the growth on secondary log file as well.

Fixes: You will need to analyse the growth on the log file at primary (since log file at secondary is in restoring mode), if the growth rate is intended to grow that big or was it some sort of AD-HOC activity which made the log file blow that big on primary that caused the similar sort of growth on secondary.

In case: log file is intended to grow that big and there are task that will continue to perform you may consider the option of going with appropriate disk/storage accordingly, because no matter you shrink the task will occupy the same space back. In case its an AD-HOC task, you can go ahead and shrink the log file at primary ( making sure latest log backup is taken and copied to secondary) which should apply the same at secondary.

  Note that it is the transaction log file of the secondary database in
  the DR site that is growing fast.

Here if you meant that primary is stable with its log file and its only the secondary which is growing then , you need to consider looking at how log shipping is configured. I don't recall such scenario where primary log holds better while secondary grows that big, unless until log shipping is not set up correctly. If that's the case, consider setting up LS again with proper settings.


How to restore a database from SQL Server 2000 to SQL Server 2012 I've got a databe in SQL Server 2000 and older than it! And it's very old version. I can't restore to SQL Server 2012. Is that because of old version? How can I restore it? Is there any script or something like that?

Thanks for your advice.

SQL Server 2012 no longer supports restoring SQL Server 2000 databases. You need to take an intermediate step:

backup your SQL Server 2000 database
restore it to SQL Server 2005 / 2008 / 2008 R2
change the compatibility level to at least "90" (SQL Server 2005)
backup again from that intermediate server
then restore that second backup on SQL Server 2012

Or depending on size and complexity of your SQL Server 2000 database, it might be easier to just script out the structure and data into SQL scripts and then run those on SQL Server 2012.


SQL server after RAID problems We had a SQL server die due to some RAID problems.  We sent the machine out to a data recovery company and they are able to recover the .MDF and .LDF files from the drives.  Will I be able to use those files to restore the databases to a new machine?
What do I need to watch out for? Thanks!

unless they are not corrupted,You can use the files to restore databases.With out restoring you cant say about usability of those files..

If you are able to restore them with out any issues

1.First take backup of what you restored
2.Do a DBCC CHECKDB to check for any signs of data corruption


Optimize PostgreSQL query to avoid two time DB call for same operation I am running following PostgreSQL query to support my service call for same filter criteria, any thoughts on how we can avoid these two DB calls for the same query and return JSON data in a single query

WITH t as (
SELECT row_to_json(z)::jsonb as record FROM
(
    SELECT  t1.A, t2.B, t1.C
 FROM Table1 t1
 JOIN Table2 t2 ON t1.id = t2.id) z)

 SELECT record FROM t
 {=filterParam} ORDER BY {=orderParam} LIMIT(250) OFFSET({=offsetParam});

WITH t as (
SELECT row_to_json(z)::jsonb as record FROM
(SELECT  t1.A, t2.B, t1.C
 FROM Table1 t1
 JOIN Table2 t2 ON t1.id = t2.id) z)

 SELECT COUNT(record) AS total_record_count From t
{=filterParam};

I am very beginner to PostgreSQL so all answers are appreciated

You can try to use such query:

WITH t as (
SELECT row_to_json(z)::jsonb as record FROM
(
    SELECT  t1.A, t2.B, t1.C
          ,COUNT(1) OVER () AS total_record_count
 FROM Table1 t1
 JOIN Table2 t2 ON t1.id = t2.id) z)

 SELECT record FROM t
 {=filterParam} ORDER BY {=orderParam} LIMIT(250) OFFSET({=offsetParam});

but your original query doesn't look like the best approach for pagination implementation. Among other factors depending on amount of data you have it may be more efficient to use separate queries. Because server would need to read all rows to calculate total_record_count for any offset and limit.


PostgreSQL update join vs SQL Server update join I recently started converting a personal project from Microsoft SQL Server to PostgreSQL and I was surprised at the abysmal performance I encountered doing an UPDATE JOIN between two tables.
Suppose they look something like:
CREATE TABLE foo (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

CREATE TABLE foo2 (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

In T-SQL I would do an update using a join using something like this:
UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;

But running in Postgres, the query is glacially slow.
If I change it to:
UPDATE foo
SET bar = t2.bar
FROM foo2 t2
WHERE foo.id = t2.id;

it's not a problem.
I get that the syntax is different but I would've expected the query optimizer to work out something in the same ball park. Instead, things go bananas. Besides the syntactical differences, is there a nuanced difference between the two queries that I fail to see?
Explain plans
Update on foo  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
  ->  Nested Loop  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
        ->  Seq Scan on foo  (cost=0.00..145721.10 rows=582410 width=1010)
        ->  Materialize  (cost=85852.43..247935.91 rows=580908 width=17)
              ->  Hash Join  (cost=85852.43..241627.37 rows=580908 width=17)
                    Hash Cond: (t1.id = t2.id)
                    ->  Seq Scan on foo t1  (cost=0.00..145721.10 rows=582410 width=10)
                    ->  Hash  (cost=75754.08..75754.08 rows=580908 width=15)
                          ->  Seq Scan on foo2 t2  (cost=0.00..75754.08 rows=580908 width=15)

Update on foo (cost=87575.47..535974.25 rows=581621 width=1022)
  ->  Hash Join  (cost=87575.47..535974.25 rows=581621 width=1022)
        Hash Cond: (foo.id = t2.id)
        ->  Seq Scan on foo (cost=0.00..151301.17 rows=1140417 width=1011)
        ->  Hash  (cost=75761.21..75761.21 rows=581621 width=36)
              ->  Seq Scan on foo2 t2  (cost=0.00..75761.21 rows=581621 width=36)

But running in Postgres, the query is glacially slow.

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id;

There is no join condition between foo and t1, the implicit CROSS JOIN forces a Cartesian product, i.e. O(N²) (!) update operations instead of just O(N). And the result is non-deterministic nonsense. The effect also becomes apparent in the query plan: rows=338326628280 instead of rows=581621 (Also: both plans were produced off slightly different tables, but that seems irrelevant to the question.)
Could be fixed by adding a join condition like:
UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id
WHERE  foo.id = t1.id;  -- !

Well, technically, a WHERE condition, but all the same.
But that's just putting lipstick on a pig. While id is the PK column of each table, that's just adding noise. Use the command you already found instead:
UPDATE foo
SET    bar = t2.bar
FROM   foo2 t2
WHERE  foo.id = t2.id;

The manual advises for the FROM clause of UPDATE:

Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

And:

When a FROM clause is present, what essentially happens is that the
target table is joined to the tables mentioned in the from_item list,
and each output row of the join represents an update operation for the
target table. When using FROM you should ensure that the join
produces at most one output row for each row to be modified. In other
words, a target row shouldn't join to more than one row from the other
table(s). If it does, then only one of the join rows will be used to
update the target row, but which one will be used is not readily predictable.

Such a self-join makes sense (or is even necessary!) if you need a LEFT [OUTER] JOIN to additional table(s). Sadly, there is no provision in SQL to say "FROM LEFT" in an UPDATE. Example:

Nullify column in update if subquery returns empty


Oracle table and number of blocks What is the easiest way of finding out the number of blocks a table (oracle) occupies and the number of extents?

Assuming there are no LOB fields or other specialties:

select sum(blocks) as blocks, count(*) as extents
from dba_extents
where owner = '&table_owner' and segment_name = '&table_name';


How to create development/test environment of TB reporting database in SQL Server I am working on a couple large reporting databases with lots of reporting and analytical queries and many ETL jobs. When I make changes I usually do in in production, be that changes in indexing or the code.
Just had a minor accident after changing some code so I am thinking to start creating a development/test environment.
But the databases are huge with some huge tables. The databases are located on a huge server with lots of CPU cores and memory. How can I make a light test environment on my own computer in a developer edition?
Another option would be to create a test environment on the production server which is a data warehouse server (i.e. there are lots of ad hoc queries anyway). Is this a better approach? I would just have to restore a backup and name the database something else...
There are many dependencies in the databases, so scripting out all the objects manually is too much work. E.g. some stored procedures use around 100 tables...
So my question is: how can I stop making all my changes directly in production?
Thanks

The best option is to have a separate Dev/Test server where you can have a full instance of  your Data Warehouse.  If you don't have a seperate server, you might use an Azure Virtual Machine (or similar) that can be turned off when not in use.
And you should also have a local dev environment, probably with a subset of the data.  This allows teams to work in parallel without stepping on each other.
If you have only one server, you can co-locate the Dev/Test database and the production database, but you have to be careful to not over-tax the resources on the server.
It's also completely appropriate to perform some Dev/Test activities directly against the production data warehouse.  For instance when you're building or modifying reports, but not when you're modifying tables, views, or stored procedures, or working on your ETL jobs.  A good rule of thumb is that if the work requires more than SELECT privileges, it shouldn't be done directly on production.


How to check oracle error description in sqlplus? I'm trying to find the oracle error's description in SQL*PLUS.  Similar command in DB2 as

? SQLXXXX

Where XXXX is the error number.

You can use oerr utility.

Example:

[oracle@ora12c Desktop]$ oerr ora 01950
01950, 00000, "no privileges on tablespace '%s'"
Cause:    User does not have privileges to allocate an extent in the
          specified tablespace.

Action:   Grant the user the appropriate system privileges or grant the user
          space resource on the tablespace.

You can search error code on this webpage Search for Error Messages or you can download the Database Error Messages documentation from here.

Note: This utility is not available on Windows.


Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query? I'm personally interested in data structures and with knowing that rowstore indexes are stored with a B-Tree behind them, I'm always curious in trying to visualize what those B-Trees look like for a given table/index.
Is there a way to correlate or expose the number of nodes in a B-Tree for a given rowstore index via the execution plan, statistics, operator properties, index / table properties etc?
Furthermore can I do the same to determine the number of nodes traversed for a particular query?
Bonus points if there's a way to determine the number of leaf nodes in the B-Tree too.

Some of this information like index depth can be found in DMF dm_db_index_physical_stats().  Some outer interesting information that can be found in it is number of used data pages and the fragmentation level. Personally I have used the following query to get the state of the indexes for current database
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,
ixs.index_type_desc,
ixs.index_depth,
ixs.page_count,
ixs.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ixs
INNER JOIN sys.indexes ind
ON ind.object_id = ixs.object_id AND ind.index_id = ixs.index_id;

More information about this DMF can be found here https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15


Disabled SA user is listed as logged in I was looking for the last time users have been logged into the system and saw SA as being used. Strange is that SA is disabled by default and not used on our systems. How is that possible, what am I missing?
Here is the code used to list users:
SELECT login_name [Login] , MAX(login_time) AS [Last Login Time]
FROM sys.dm_exec_sessions
GROUP BY login_name;
And here is the SA listed as disabled and as logged in:

Thanks.

This is because some "system" session are showing "sa" as the username.
I think you can add a
where is_user_process = 1

in your query to exclude those system session and you should no longer see "sa" as a connected user.


cannot delete db table I tried to use a DROP statement to delete a table but I could not.Instead I tried the following....I deleted the .frm and .ibd files from of the respective table from the directory.

Then I used a create statement from a backup file to recreate the table...and the weird thing is that I get message saying that the table already exists (despite having deleted the aforementioned files)...

After the create statement the directory has again the .ibd file.

I do not know what to do or assume.

First have a look at the InnoDB Architecture (from Percona CTO Vadim Tkachenko)

Your problem is very simple. You have a broken data dictionary entry. Inside the system tablespace file ibdata1 is a list of tablespace ids.

There is an entry for appointments.business_users. Deleting the .frm and .ibd of appointments.business_users does not remove its tablespace id from the data dictionary. You need to remove that entry from the data dictionary.

The reason you are getting the error still ? Although you can the CREATE TABLE and generated the .frm and .ibd file, the tablespace id in the .ibd file does not match the original tablespace id in the data dictionary.

Here are some old posts on how to repair it in place

From @akuzminsky : InnoDB issue after power outage a few weeks ago - no backup
From @DerekDowney : ERROR Cannot find or open table?
From me : restore table from .frm and .ibd file?

If the database isn't too big, you could do the following:

mysqldump everything EXCEPT appointments.business_users
drop all databases (except mysql schema)
shutdown mysql
delete ibdata1
start mysql (recreated ibdata1)
reload the mysqldump
manually run the CREATE TABLE command for appointments.business_users

Either way, you have some homework to do.


Is there any way to turn off the "loading" message when a page refreshes in Reporting Services? I have a report that auto-refreshes every 30 seconds.  Usually this happens fast enough that you don't even notice.  Sometimes, however, the load takes a bit longer.  When this happens, the existing data is "greyed out" a bit, and a animated box that says "Loading" appears.

Is there any way to prevent the data from beign "greyed out"? I find it very distracting.

The report is part of a monitoring tool.  We have it on a jumbo-tron.  Under certain conditions, it changes color, which catches people's attention.  The "greying out" also catches people's attention but for the wrong reason.

If you have an ASP.NET developer on hand, have them use the ReportViewer control to embed the report directly into a plain old aspx page. I've done this to a few reports, and they never show the usual "Report is being generated" message that you get when accessing them via Report Manager. (You might have to set AsyncRendering="False" on the ReportViewer control to get the desired behavior.)

This will also allow you to dress up the page a bit, and conceal the various Reporting Services toolbars, which would be ideal for a billboard display like you describe. Put a meta refresh in the page to get a 30-second refresh, and you're set.


How do I fix the Code Page in SSIS Lookup Transformation to be 65001? I have an SQL server 2019 with databases and tables all set with the collation  Latin1_General_100_CI_AS_SC_UTF8.
The relevant table has code and desc columns which are both of data type varchar.
In SSIS project I have a single Data Flow component:
I have a UTF-8 CSV file read with flat file connection, text column code to match is DT_STR, 65001
I have a Lookup that is set to "Full Cache" and loads the Latin1_General_100_CI_AS_SC_UTF8 table, but SSIS thinks the varchar columns are DT_STR, 1252
Finally the code in both CSV and lookup are matched and desc is sent to destination table which is on the same Latin1_General_100_CI_AS_SC_UTF8 collation. The destination component is set to AlwaysUseDefaultCodePage True and DefaultCodePage 65001.
I now get an error saying the column has more than one code page and cannot run the package.
If not for the mislabeled 1252, this package should run. I believe its something to do with ExternalMetadataXml, which is read-only and says all my lookup varchar columns are CodePage="1252".
If I manually edit the package .dtsx with npp and replace all instances of 1252 with 65001, the package can run and seems to do what I expected, as long as I never touch the lookup component again.. That seems a bit messed up of a solution tho, I am hoping there's someone else who has a cleaner way to fix this. Thanks.
edit 2021/03/10: I thought I should clarify, the lookup is using OLE to read a table from the SQL server, and is trying to match code column onto the code column read from the flat file. The problem is not the flat file, which is already recognised as DT_STR, 65001. The problem is the Lookup widget reading from a table that should be 65001, somehow ends up with 1252. If I didn't use OLE, and instead read from a UTF8 flat file to create the Lookup widget, there is no error.

With the disclaimer that I'm a "dumb American" who doesn't deal with non-English data but did work with a friend recently on using bulk import with UTF-8 data, here's what I see.
I have a pipe separated value file that looks like this
level|name
7|"Ovasino Poste de Santé"

Notepad++ indicates I have saved it as UTF-8.
I created two flat file connection managers in SSIS: Codepage65001STR and Codepage65001WSTR. They both use a Code Page of 65001 (UTF-8)

In the advanced tab for the STR variant, I left the data type as DT_STR

In the advanced tab for WSTR variant, I changed the data type to DT_WSTR

I also created a table and loaded it with the same data
DROP TABLE IF EXISTS dbo.dba_286478;
CREATE TABLE dbo.dba_286478
(
level int NOT NULL
,   name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8
)
INSERT INTO dbo.dba_286478
(
    level
,   name
)
VALUES
(
    7 -- level - int
,   'Ovasino Poste de Santé' -- name - varchar(75)
);

DROP TABLE IF EXISTS dbo.dba_286478;
CREATE TABLE dbo.dba_286478
(
    level int NOT NULL
,   name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

I then created a data flow task with a Flat File Source using the different Flat File Connection Managers and added data viewers between them and an empty derived column (so I had an anchor point for the data viewer).
I did the same thing with an OLE DB Source pointing at my table as well as a custom query of
SELECT
    T.level
,   CAST(T.name AS varchar(75)) AS name
FROM
    dbo.dba_286478 AS T;

as well as explicitly defining the collation as it makes no different in SSIS
,   CAST(T.name COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS varchar(75)) AS name

The results all show the same, the final word is an accented Sante. If the UTF-8 hadn't happened, it'd show as SantÃ©

At this point, it doesn't matter whether we DT_STR or DT_WSTR in our flat file source column definition, the component understands UTF-8 and UTF-16.
Properties, metadata of each. The Codepage 65001 STR looks as expected. code page of 65001 and data type DT_STR

Unicode, DT_WSTR looks good

The OLE components however, they're a different animal. The component is returning a metadata of DT_WSTR (full Unicode/UTF-16) regardless of whether we do an explicit cast to DT_STR, optionally specifying collation, or let the natural metadata flow through.
Either way, it doesn't detect the code page/collation stuff and just says Nope, you're Unicode

So, when we get to trying to use a Lookup task with an OLE DB connection manager, we can expect and receive the same inability to delineate between UTF-8 string/varchar and UTF-16/nvarchar

The error would indicate and that's true, DT_STR can't match DT_WSTR

Cannot map the input column, 'name', to the lookup column, 'name', because the data types do not match.

So what do I do?
You must have type alignment to make the lookup component work which means the source data needs to be of type DT_WSTR. You can either bring the data in from the Flat File as Unicode or leave it as string with code page 65001. If you go the latter route, then you need to make a copy, Derived Column or Data Conversion work, of that column and use it in the Lookup component.
If you're pulling text out of the lookup component, that's now in your pipeline as Unicode so you probably want to then convert that to a string type with code page. Again, Derived Column or Data Conversion will be used.

SSIS OLE components don't understand UTF8
We saw with the source and lookup component that SSIS is going to treat the UTF-8 strings as UTF-16 but I assumed it would handle storing to the table just fine. Not so much.
My server collation is Latin1_General_100_CI_AI_SC_UTF8 and while I switched accent sensitivities between server and table definition of dbo.dba_286478, it doesn't matter in this case as it's UTF-8 all the way down.
For my Flat File Source, I use the STR based file which has the metadata shown above with the yellow highlighting. The Codepage 65001 for data type DT_STR is what we want.
I added an OLE DB Destination and pointed it at my table which again has the "name" column defined as UTF-8
name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8

Check this error!

Validation error. Data Flow Task OLE DB Destination [138]: The column "name" cannot be processed because more than one code page (65001 and 1252) are specified for it.

We only have code page 65001 at play in this data flow and yet, something in SSIS space is inferring/defaulting to a 1252 code page during validation.
Making it work
The componentry in a data flow task was built with OLE DB connections in mind. That's why the Lookup task supported OLE DB Connections for 2005, 2008 and maybe 2008R2? Long time ago now, I know but the Cache Connection Manager (aka anything else) option was added in later iterations because of the need to use something besides OLE connection managers especially given the push then was to deprecate the OLE driver.
An ADO.NET Connection Manager does slightly better than OLE in this case and that's likely what you're going to have to use to work with UTF8 data in an SSIS package. It will be implicitly converting to UTF-16 when it presents to the table and then SQL Server is going to snap it back into UTF-8 space (best I can tell).
For reference, bringing UTF-8 data into the pipeline with ADO Source will still be flagged as DT_WSTR/UTF-16/unicode.

But you can land DT_STR code page 65001 to an ADO.NET Destination without a code page mismatch error like I'm seeing for OLE DB Destination.

The data from the database is going to appear as DT_WSTR regardless of how you bring it into the pipeline. That means you can define and OLE and an ADO connection manager to use the Lookup component as is.
Or you can add a precursor data flow step to populate the Cache Connection Manager and only have an ADO.NET connection manager. Were you to go that route, convert the DT_WSTR data to DT_STR with codepage 65001 and store that data into the cache.
DFT - Populate Cache -> DFT - Load data

DFT - Populate Cache
ADO.NET Source -> Data Conversion -> Cache Connection Manager

DFT - Load Data
Flat File Source -> Lookup Component -> ADO.NET Destination


Mysql join query taking too much time even though indexes are implemented Hi I have a mysql query

SELECT
    `locationMapping`.`keyId` AS `caseId`,
        `locationMapping`.`id`
FROM
    `locationMapping`
INNER JOIN `locationSubtype` ON (`locationMapping`.`locationSubtypeId` = `locationSubtype`.`id`
    AND `locationMapping`.`locationTypeId` = `locationSubtype`.`locationTypeId`
    AND `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
    AND ((`locationSubtype`.`govtId` = '90')
    AND (`locationMapping`.`keyTable` = 'xercase'))
WHERE
    locationMapping.govtId = 90

GROUP BY `keyId`

Taking 8 seconds to execute.

Create table command for locationMapping is:
    show create table locationMapping;

CREATE TABLE `locationMapping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationSubtypeId` int(11) NOT NULL DEFAULT '0',
`keyId` int(11) NOT NULL DEFAULT '0',
`keyTable` varchar(100) NOT NULL DEFAULT '',
`locationTypeId` int(11) DEFAULT '0',
`gisCode` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationmapping_instance` (`keyId`,`keyTable`),
KEY `locationmapping_subtype` (`locationSubtypeId`),
KEY `locationmapping_govtid` (`govtId`)
) ENGINE=InnoDB AUTO_INCREMENT=3994710 DEFAULT CHARSET=latin1

Create table command of locationSubType is

CREATE TABLE `locationSubtype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationTypeId` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
`code` varchar(30) DEFAULT NULL,
`gisCode` varchar(100) DEFAULT NULL,
`sortorder` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationsubtype_locationtype` (`locationTypeId`),
KEY `locationsubtype_govtid` (`govtId`),
KEY `locationsubtype_giscode` (`gisCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4319 DEFAULT CHARSET=latin1

Explain statement for query is:

id,   select_type, table,             type,  possible_keys,                                                             key,                      key_len, ref,                              rows, Extra
'1',  'SIMPLE',    'locationSubtype', 'ref', 'PRIMARY,locationsubtype_locationtype,locationsubtype_govtid',             'locationsubtype_govtid', '4',     'const',                          '756', 'Using index condition; Using temporary; Using filesort'
'1', 'SIMPLE',     'locationMapping', 'ref', 'locationmapping_instance,locationmapping_subtype,locationmapping_govtid', 'locationmapping_subtype','4',     'comcate_dev6x.locationSubtype.id','333', 'Using where'

Try this rewrite:

SELECT  m.`keyId` AS `caseId`,
        m.`id`
    FROM  `locationMapping` AS m
    WHERE  m.`keyTable` = 'xercase'
      AND  m.govtId = 90
      AND  EXISTS (
        SELECT  *
            FROM  locationSubtype
            WHERE  m.`locationSubtypeId` = `id`
              AND  m.`locationTypeId` = `locationTypeId`
              AND  m.`govtId` = `govtId`
                  )
    GROUP BY  m.`keyId`

In many cases EXISTS() is faster than INNER JOIN.

This 'composite' index is important:

locationMapping: INDEX(govtId, keyTable)  -- in either order

The Optimizer is smart enough to notice the transitive nature of govtId=90, so I removed one mention of it.

More

AND   `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
AND ((`locationSubtype`.`govtId` = '90')
...        locationMapping.govtId = 90

The optimizer, if given the first of those, plus either of the other two, will deduce the final one.  You have provided all 3, which is OK.  But it is 'overkill'.

The first line is important because it tells the optimizer how the two tables are related.


I see a constraint in my table thats not in INFORMATION SCHEMA? When I expand the database "Constraints" object, I see 2 of them named DF_DocumentD_Docum_3F466844 and df_documentdistqty, but when I select to view everything from the "Information Schema.TABLE Constraints", the constraints named above dont show up, any idea why?
I scripted out these constraint and it has the following info:

ALTER TABLE [dbo].[DocumentDistribution] ADD  DEFAULT (getdate()) FOR [DocumentDistDate]
GO

and the other has:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[df_documentdistqty]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DocumentDistribution] DROP CONSTRAINT [df_documentdistqty]
END

Please stop using INFORMATION_SCHEMA and deprecated backward compatibility views like sysobjects. This is just another in the long list of things that INFORMATION_SCHEMA is missing in order to provide a complete picture of your metadata. See this post for a more exhaustive list:

The case against INFORMATION_SCHEMA views

To find the name of the default constraints for any table, use the catalog view sys.default_constraints:

SELECT [constraint] = df.name, [column] = c.name, df.[definition]
  FROM sys.default_constraints AS df
  INNER JOIN sys.tables AS t
    ON df.parent_object_id = t.[object_id]
  INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    AND df.parent_column_id = c.column_id
  INNER JOIN sys.schemas AS s
    ON s.[schema_id] = t.[schema_id]
  WHERE s.name = N'dbo'
  AND t.name = N'DocumentDistribution';


Database Mirroring and Snapshots As part of a SQL Server deployment, it was proposed to use Database mirroring as a redundancy option as well as allowing for Reporting and MI to be detached from the Operational DB.

The proposal is that we setup the database mirror using asynchronous mirroring and then at regular intervals create snapshots.

However one line in MSDN states:

  To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.

How does this work in practice? And what does this mean exactly?

How often will the database be in a non-synced state? (In a relatively moderate volume workload Database (~50 users).)

Will a create snapshot script wait until the DB is synchronized before executing, or do we need to prompt the DB to fully synchronize before the snapshot is taken (as part of the create snapshot script)?

asynchronous mirroring
It means, a transaction is committed on the Principal server before being copied over to the Mirror Server.

Whereas in Synchronous Mode Principal Server send the logs to the Mirror server and waits for the signal from the Mirror Server, once the transactions has been applied to the Mirror Server only then it commits the transaction on the Principal Server. Also the transaction Safety needs to be set to full and you to have a Witness Server.

Asynchronous mirroring does not effect the performance of Principal Server whereas other operating modes in Database Mirroring will have effect on the performance of the principal server as in Synchronous Mode both the principal and Mirror server are in the same state and you can have a long Redo Queue (Pending Transactions to be rolled-Forward on Mirror Server).

Note: Asynchronous mirroring is only available in the Enterprise Edition.

Reporting with snapshot script
Have you considered SNAP SHOT Replication for the reporting purposes ???


How to scale with MySQL (when not ready to scale properly) We are using MySql.

My situation

I have a large number of tables with millions of rows each. Those tables are updated every second and are used both for adding info and retrieving info. Each table can be 5GB or 10GB or even more.
I have one table that I keep sums of information (something like a summary table of the information I need) but this is starting to get big in size as well.

My limitations

at the moment I cannot change database due to various reasons (mainly no knowledge, time and budget)
all the extra power that we add to the server goes to other resources needed so I cannot run very heavy queries

Temporary ways I have thought for scaling

Having these things in mind I am trying to think of ways to scale with what I have:

For the tables with millions of rows I have thought to keep to separate databases (could make my life easier for backups / exports / changes). Keep my main data in 1 database and all peripherals (huge tables) to other databases. Let’s say have a different database for a different need.
For the problem with the table that I really need regularly and is growing fast I was thinking into splitting it into XX tables. Could be 1 table per user (which might be too much) or 1 table per XXX users.

Are these ideas totally crazy and really bad DB design?

If yes..... any suggestions other than changing everything at once?

PARTITIONing does not save space.  Each partition has 4M-7M of "free" space.  That can add up.  Partitioning can be used for removing data from the server (or moving to another server) "old" data.  More discussion:  http://mysql.rjweb.org/doc.php/partitionmaint

Nor is any ad hoc partitioning likely to help.  That is splitting tables up, moving between databases (I'm talking about CREATE DATABASE, not server) will not change performance, nor space.  On the other hand, "easier for backups / exports" may be a valid reason.  Please provide details so we can discuss.

"Sharding" is complex, but it is the way to split data across multiple servers.  (Note: I am using "server" to mean a separate physical machine.)

A small fix is to shrink the datatypes in tables.  Are you using BIGINT (8 bytes) when MEDIUMINT (3 bytes) would suffice?  Etc, etc.

Summary tables are an excellent way to save space and speed up "reports".  But perhaps they are not as small as they could be?  Let's see SHOW CREATE TABLE.

"growing fast I was thinking into splitting it into XX tables" -- NO, NO, NO!  The only exceptions is when "sharding".

How do you populate the Summary Table(s)?  Some tips:  http://mysql.rjweb.org/doc.php/summarytables  I assume your summary tables are updated either in realtime, or nightly?  And you update them only incrementally, not by starting over.

You say 1 insert per second?  That is a very low rate.  Perhaps it is faster than that?

When the data is properly indexed and/or summarized, the size of the table does not matter (for performance).  Are you concerned about disk space or speed?


Derived Column Error in SSIS I am using the SSIS package to populate data into the DimSuppliers table. However, this throws the following error whenever it reaches the Derived Column Transformation section.

  [Derived Column [2]] Error: SSIS Error Code
  DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Derived Column" failed
  because error code 0xC0049067 occurred, and the error row disposition
  on "Derived Column.Outputs[Derived Column
  Output].Columns[SupplierFirstName]" specifies failure on error. An
  error occurred on the specified object of the specified component.
  There may be error messages posted before this with more information
  about the failure.

I am trying to split the ContactName in the source table into

(SupplierFirstName)

SUBSTRING(ContactName,1,FINDSTRING(ContactName,",",1) - 1)

(SupplierLastName)

 SUBSTRING(ContactName,FINDSTRING(ContactName,",",1) + 1,FINDSTRING(ContactName,",",2) - FINDSTRING(ContactName,",",1) - 1)

I also included this procedure to check the null on the insert values

USE [NorthWindDW]
GO

/****** Object:  StoredProcedure [dbo].[Insert_New_Suppliers]    Script Date: 09/01/2017 23:14:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Insert_New_Suppliers]

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    IF NOT EXISTS
    (
        SELECT * FROM [dbo].DimSupplier
        WHERE SupplierKey = -1
    )

    BEGIN
    SET IDENTITY_INSERT [dbo].[DimSupplier] ON
    INSERT [dbo].[DimSupplier] (SupplierKey, SupplierId, SupplierFirstName, SupplierLastName, Position, SupplierCountry, PostCode, Telephone) VALUES
    (-1,-1,'Unknown','Unknown','Unknown', 'Unknown', 'Unknown','Unknowm');
    SET IDENTITY_INSERT [dbo].[DimSupplier] OFF
    END
END

GO

DimSupplier Table

Create Table [dbo].[DimSupplier]
(SupplierKey         INT IDENTITY NOT NULL,
 SupplierId          INT NOT NULL,
 SupplierFirstName   NVARCHAR(50) NOT NULL,
 SupplierLastName    NVARCHAR(50) NOT NULL,
 Position            NVARCHAR(50) NOT NULL,
 SupplierCountry     NVARCHAR(50) NOT NULL,
 PostCode            NVARCHAR(25) NOT NULL,
 Telephone           NVARCHAR(25) NOT NULL);
Go

Am I doing something wrong?

Joanolo pointed out if the name had a comma which was not in my case. Therefore, I have changed the format of the derived columns expression to the ContactName as follows:

SupplierFirstName

SUBSTRING(ContactName,1,FINDSTRING(ContactName," ",1) - 1)

SupplierLastName

SUBSTRING(ContactName,FINDSTRING(ContactName," ",1) + 1,LEN(ContactName) - FINDSTRING(ContactName," ",1))


Index rebuild after SQL Server upgrade to 2014 The company I work for decided to upgrade the database engine from SQL Server 2005 to SQL Server 2014, the database is big and uses multiple files, and each table has a complex set of indexes to insure fast performance, and we are calculating the time required to upgrade, one missing bit of information I didn't find an answer for is:

Do we have to rebuild indexes after we upgrade the databases?

You can just rebuild indexes as you normally would as part of your standard maintenance schedule, this doesn't need to be done specifically due to the upgrade. That being said it depending the size of your tables\indexes and how much time you have for your upgrade, it can't hurt to start with fresh rebuilt indexes if you can work it into your upgrade plan. But no, it's not a requirement.

You definitely need to update your statistics however, as this can lead to poor plan generation by the optimizer after upgrading. This needs to happen before you do any index rebuilding. I'll try and dig up the link I had that gave a more detailed explanation.

Here is a great article by Thomas LaRock that can serve as a basic upgrade checklist to build on, to ensure you haven't missed anything:

http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/


Optimised way to search over 2 milllion poi data in mysql We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4

select *, ( 3959 * acos( cos( radians(
-- latitude
@my_lat) ) * cos( radians(
destination.latitude ) ) * cos( radians(
destination.longitude ) - radians(
-- longitude
@my_lon) ) + sin( radians(
-- latitude
@my_lat) ) * sin( radians(
destination.latitude ) ) ) ) AS distance
-- table containing targets to compare distance
from destination
order by distance limit 1
;

The problem we find this taking too long. Some forum suggest to limit the search based on +1 and -1 the lat and long value. The problem now we need to provide minimal one poi. So any optimisation help on this or better way of doing this?

You can use Geohashing (Geohasing)

You have to create a geohash for every POI in your table.

And then based on the precision that you choose you can use
LEFT(your_geohash_column,'c1','c3','c4','c5','c6',c7','c8')
Where the c's are the 8 neighbors from the center calculated by your desired precision.

Keep in mind that you are limiting the results with the precision which you can consider it a radius.


Help me tune the long-running query I have a problem with the following query executing from the application (Microsoft Dynamics AX):

DECLARE @p1 INT;
SET @p1 = NULL;
DECLARE @p2 INT;
SET @p2 = 0;
DECLARE @p5 INT;
SET @p5 = 2 + 4096;
DECLARE @p6 INT;
SET @p6 = 8193;
DECLARE @p7 INT;
SET @p7 = 0;
EXEC sp_cursorprepexec
     @p1 OUTPUT,
     @p2 OUTPUT,
     N'@P1 nvarchar(5),@P2 int,@P3 nvarchar(5),@P4 int,@P5 nvarchar(5),@P6 nvarchar(5),@P7 datetime,@P8 nvarchar(21),@P9 numeric(28, 12)',
     N'SELECT A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ FROM INVENTTRANS A,INVENTTRANS B,INVENTTABLE C WHERE ((A.DATAAREAID=@P1) AND (A.TRANSTYPE=@P2)) AND ((B.DATAAREAID=@P3) AND (((B.INVENTTRANSID=A.INVENTTRANSIDFATHER) AND (B.TRANSREFID=A.TRANSREFID)) AND (B.TRANSTYPE=@P4))) AND ((C.DATAAREAID=@P5) AND (C.ITEMID=B.ITEMID))
AND EXISTS (SELECT ''x'' FROM INVENTSETTLEMENT D WHERE ((D.DATAAREAID=@P6) AND ((((D.TRANSDATE=@P7) AND (D.VOUCHER=@P8)) AND (D.TRANSRECID=A.RECID)) AND (D.COSTAMOUNTADJUSTMENT<>@P9))))
 GROUP BY A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ ORDER BY B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_',
     @p5 OUTPUT,
     @p6 OUTPUT,
     @p7 OUTPUT,
     N'dat',
     8,
     N'dat',
     2,
     N'dat',
     N'dat',
     '2017-05-17 00:00:00.000',
     N'IM17008141934',
     '0.000000000000';
EXEC sp_cursorfetch
     @p2,
     2,
     1,
     1;

The estimated plan for this query is here

XML version here

The query keeps running for almost 3 hours. I have also captured metrics from WhoIsActive sp for this queries session:

wait_info                       (2ms)PAGEIOLATCH_SH:ecc_wrk:4(*)
CPU                             88,765
CPU_delta                       47
tempdb_allocations              0
tempdb_current                  0
tempdb_allocations_delta        0
tempdb_current_delta            0
blocking_session_id             NULL
blocked_session_count           16
reads                           26,610,593
reads_delta                     20,903
writes                          0
writes_delta                    0
physical_reads                  1,212,764
physical_reads_delta            418
used_memory used_memory_delta   276
status                          0
open_tran_count                 1
host_name                       ***
database_name                   ***
program_name                    Microsoft Dynamics AX

As I can see it reads huge amount of data and executes for enormous amount of time.
I also captured the query and executed it in management studio where it took 1 second to complete. The actual execution plan is here:

XML version here

I cannot understand what the problem might me and where to dig. Any help on what to do next will be deeply appreciated.

First of all, the code you posted is not the code executed by the application. There is no Fetch Queryoperator so you aren't retrieving any rows.

As you noted in chat you constructed the code yourself but it's hard to guess what exactly will be the code executed by AX, that depends on some settings and calculations done by the AX kernel.

First of all, your cursor is likely to be FAST_FORWARD and the number of rows fetched could either be everything at once, or a number of records at a time.

The number of rows fetched at once is defined by the Maximum Buffer Size parameter/the row size. See Microsoft Dynamics AX 2012 Server Configuration

The cursor definition will look something like this

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

or this

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
--
-- Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)
--
set @p5=16+4096+8192
declare @p6 int
set @p6=8193
declare @p7 int
--
-- Number of Rows for AutoFetch.
-- This is calculated by Maximum Buffer Size (24K default) / Row Length
--
set @p7=4
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(21)',N'SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))',@p5 output,@p6 output,@p7 output,N'ceu',N'SO-100004'
-- @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7

That being said, most of the time when I get a poor performing query in AX the query behaves the same with or without a cursor, and it's rare that I need the actual code including the cursors to be able to reproduce the problem in Management Studio, however if you really need the cursor definition the easiest is if you can reproduce it in a test environment.

Once you can reproduce the issue you need to locate the offending query in code.
If you can reproduce the issue in a test environment (because AX is fairly prone to parameter sniffing) you can use the built-in SQL Statement trace log to find the call stack for the query.

You can also look into the Trace Parser tool to capture traces with the actual parameter values and the code trace up to the query. That can help a lot too.

Once you know where the query is executed you could put a breakpoint just before the query and when you hit it fire up SQL Server Profiler to capture the exact code being executed while you step through the select in your AX debugger.

Once you have the problematic code (if you really need it including cursors) the regular tuning principles are valid.

Your first attempts should be indexing because your options to change the query are a bit limited in AX. Then again the estimated plan you posted only has an estimated cost of 0.04 and uses index seeks I'm not sure this is your problem.

AX is very prone to parameter sniffing because of all the select * resulting in a lot of key lookups.

You could try to use some keywords to influence the plan, namely forceliterals to disable parameterization and force a new plan to be compiled for each execution or you could set some row goals using firstfast and that's about it.

See the Select statement syntax documentation to see which options you have, but don't overdo it with the keywords (as I suggested in my answer to your other question). My first guess is you are running into parameter sniffing.


How to grant permissions to database but restrict to certain schemas? I am new to database administration and work within a small team of data analysts trying to modernise. We have recently set up an Azure SQL DB server we are accessing using SQL Server Management Studio.

We have an admin account and I have created a user login for each of us for day-to-day work. I would like all user logins to have the ability to create, drop, select etc. freely for all objects where the schema is unspecified or specified as "dbo" but to only have read-only permissions where the schema is specified as  "testschema". I have attempted this using the following code from the admin account:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'testschema'))
BEGIN
    EXEC ('CREATE SCHEMA [testschema] AUTHORIZATION [dbo]')
END

---- Create permissions group:
EXEC sp_addrolemember 'team_member', 'Mel';

-- GRANT BLANKET PERMISSIONS
GRANT CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES, UPDATE ON SCHEMA::dbo TO team_member;

-- REVOKE PERMISSIONS TO SPECIFIED SCHEMAS
DENY CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES,
          UPDATE ON SCHEMA::testschema TO team_member;

-- ALLOW TO SELECT AND VIEW ON THESE SCHEMAS
GRANT SELECT, VIEW DEFINITION ON SCHEMA::testschema TO team_member;

I have been playing around with granting, revoking and denying permissions to myself all morning but I can't seem to get the permissions settings correct so that my user account can create and drop tables with the "dbo" schema and see and select from tables with the "testschema" schema, but can't drop/alter/delete tables with the "testschema" schema.

Can anyone advise on the correct syntax for doing this?

EDIT: I have altered my original code slightly to DENY rather than REVOKE and instead of general permissions I have set dbo-specific schema permissions. The permissions are given as follows and appear to be correct, but team_member users still can not see or select from any testschema tables (there are no other currently-used schemas beyond "testschema" and "dbo").

A combination of DENY and a GRANT works for me.

For example:

GRANT SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
DENY SELECT ON schema::[schema_1] TO [user_name]
GRANT SELECT ON schema::[schema_Safe] TO [user_name]

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;


SQL Server Service Account on Windows Server 2016 Workgroup Cluster I am trying to install SQL Server 2016 failover clustered instance on a Windows Server 2016 workgroup cluster. I have tried using a virtual account and also a local account that exists on both nodes.

Both types of account throw the error:

  The specified Service Account is not valid. Please provide a domain account to continue

What type of account should I use for installing SQL Server 2016 failover clustered instance on a Windows Server 2016 workgroup cluster?

It turns out that only Availability Groups are supported on workgroup clusters in 2016. SQL FCI is not supported. You must use a domain.


High Disk IO, How to mitigate? I'm a programmer, not a DBA.  Be gentle :)

Overview

InnoDB, MySQL
mod_perl script, persistent connections
script called every 20 seconds by thousands of users

Problem

High Disk IO (presumably caused by updates[?]) slows everything down, creating a huge bottleneck.

Queries

UPDATE [single table] SET refreshTime to current timestamp, with two same table checks in the WHERE clause
SELECT COUNT(*) [four table join, with indexes], and a bunch of ANDs in the WHERE clause (still pretty simple)
SELECT a,b [four table join, same four tables], and a bunch of ANDs in the WHERE clause (also pretty simple)

Query cache is on.

Solutions?

I'm not a DBA, but I suspect that it's possible to have a table in RAM that periodically (every 10 seconds?) updates onto disk, and in the event of a catastrophic failure, will automatically populate the RAM table from the disk table upon restart, but I have no idea if it's actually possible, if it's the best solution or what other options there are out there.
Any thoughts or suggestions?  Again, I'm a programmer so if someone either knows someone who does this for a fee or can point me to very specific resources, I'd be very appreciative.

~~~~~~~

CREATE TABLE `openInvitations` (
`id` int(99) NOT NULL auto_increment,
`createTime` timestamp NULL default NULL,
`repAcceptTime` timestamp NULL default NULL,
`rep_id` varchar(64) NOT NULL default '',
`repRefreshTime` timestamp NULL default NULL,
`customer_macAddr` varchar(14) NOT NULL default '',
`customerRefreshTime` timestamp NULL default NULL,
`stage` char(1) NOT NULL default 'P',
`parent` varchar(25) default NULL,
`reason` varchar(64) default NULL,
PRIMARY KEY  (`rep_id`,`customer_macAddr`),
UNIQUE KEY `id` (`id`),
KEY `customer_macAddr` (`customer_macAddr`),
CONSTRAINT `openInvitations_ibfk_1` FOREIGN KEY (`rep_id`) REFERENCES `rep` (`id`),
CONSTRAINT `openInvitations_ibfk_2` FOREIGN KEY (`customer_macAddr`) REFERENCES `customer` (`macAddr`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  oi  ref     PRIMARY,customer_macAddr    customer_macAddr    16  const   1   Using where; Using index
1   SIMPLE  r   eq_ref  PRIMARY,FK_rep_1    PRIMARY     66  xxx.oi.rep_id   1   Using where
1   SIMPLE  s   eq_ref  PRIMARY,FK_subscriber_1     PRIMARY     27  xxx.r.subscriber_id     1   Using where
1   SIMPLE  c   eq_ref  PRIMARY     PRIMARY     4   xxx.s.charge_id     1   Using where

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  oi  ref     PRIMARY,customer_macAddr    customer_macAddr    16  const   1   Using where
1   SIMPLE  r   eq_ref  PRIMARY,FK_rep_1    PRIMARY     66  xxx.oi.rep_id   1   Using where
1   SIMPLE  s   eq_ref  PRIMARY,FK_subscriber_1     PRIMARY     27  xxx.r.subscriber_id     1   Using where
1   SIMPLE  c   eq_ref  PRIMARY     PRIMARY     4   xxx.s.charge_id     1   Using where

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  openInvitations     ALL     customer_macAddr    NULL    NULL    NULL    5258    Using where

After fixing query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  openInvitations     ref     customer_macAddr    customer_macAddr    16  const   1   Using where

As DTest pointed out, provide more information about your problem.

Regarding caching, you could possibly increase your innodb buffer pool size to allow more data and indexes to be cached in memory. If you have frequent updates, you may not benefit from the query cache and might be better off using that RAM for innodb buffer pool.

If you want to put all your data and indexes in RAM, then MySQL Cluster might be your answer.

EDIT
It looks like your SELECT statements are using proper indexes. Could you provide an explain plan for your update statement? You will have to rewrite it as a SELECT statement to do that. Do a SELECT * FROM same table and with same where clause as your UPDATE statement.


Help wanted: Special case of changing columns and rows MySQL Im beginner in MySQL and want to convert this table

Any help? Thanks

You could make it with CASE and UNION, but only if your Field column has fixed values:

SELECT
    T2.Field,
    T2.S,
    T2.N,
    T2.NA,
    T2.P
FROM (SELECT
            'C1' AS Field,
            SUM(CASE WHEN T.C1='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C1='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C1='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C1='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'C2' AS Field,
            SUM(CASE WHEN T.C2='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C2='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C2='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C2='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'C3' AS Field,
            SUM(CASE WHEN T.C3='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C3='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C3='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C3='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'Cn' AS Field,
            SUM(CASE WHEN T.Cn='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.Cn='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.Cn='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.Cn='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T) AS T2;

Test:

mysql> SELECT  T2.Field,         T2.S,         T2.N,         T2.NA,         T2.P      FROM (SELECT 'C1' AS Field, SUM(CASE WHEN T.C1='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C1='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C1='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C1='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'C2' AS Field, SUM(CASE WHEN T.C2='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C2='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C2='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C2='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'C3' AS Field, SUM(CASE WHEN T.C3='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C3='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C3='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C3='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'Cn' AS Field, SUM(CASE WHEN T.Cn='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.Cn='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.Cn='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.Cn='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T) AS T2;
+-------+------+------+------+------+
| Field | S    | N    | NA   | P    |
+-------+------+------+------+------+
| C1    |    1 |    1 |    1 |    1 |
| C2    |    2 |    1 |    0 |    1 |
| C3    |    3 |    1 |    0 |    0 |
| Cn    |    0 |    3 |    1 |    0 |
+-------+------+------+------+------+
4 rows in set (0.00 sec)

mysql>


Useful guide & FAQ for Application+SQL Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know where to find that precious useful information.

At the moment I'm making an application which deals with a DB connection.
And I'm not sure when I want my functions to execute any queries do I pass the already made connection as a parameter to that function, or do I store the connection as a global variable, or do I just make a function for temporary connection establishment and then run that function every time before my query.

This applies to any language I guess, but at the moment I'm using Python & PostgreSQL.
Just would like to know where to find this and similar useful info which all programers probably use on daily basis. I guess that not all of you are reading 300+ page books to find this info, so there probably should be some other places.

Thanks to you all in advance!

P.S. An example:

So rather than having something like this:

global somevar
randomfunc2()

def randomfunc1():
    _do_something_with_somevar

def randomfunc2():
    _do_something_with_somevar
    randomfunc1()

To have this:

somevar
randomfunc2(somevar)

def randomfunc1(somevar):
    _do_something_with_somevar

def randomfunc2(somevar):
    _do_something_with_somevar
    randomfunc1(somevar)

I assume the second one is better?

Are there any places where I can find these tips/guidances? :)

I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there.

As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session.

For web apps, you want to open a connection at the last possible moment, and close it as early as possible. You might read from 30 tables in a page request though, so use the same connection in that time.

Database connections are "expensive" in terms of memory and startup time, so one creates a pool of them. You don't really close your db connection, you just release it back to the pool.


Get the 2nd or 3rd occurrence of a value in a delimited string I have the following table:

==========================================================
|  Name_Level_Class_Section   |   Phone Num              |
==========================================================
|  Jacky_1_B2_23              |  1122554455              |
|  Johnhy_1_B2_24             |  1122554455              |
|  Peter_2_A5_3               |  1122554455              |
==========================================================

I am thinking of simplified my SQL statement as follows:

select
    *,
    substring(Name_Level_Class_Section,
              CHARINDEX('_',Name_Level_Class_Section,
              (CHARINDEX('_', Name_Level_Class_Section) + 1)) + 1,
      CHARINDEX('_',Name_Level_Class_Section,
     (CHARINDEX('_',Name_Level_Class_Section,
     (CHARINDEX('_',Name_Level_Class_Section)+1))+1))-
      CHARINDEX('_',Name_Level_Class_Section,
     (CHARINDEX('_',Name_Level_Class_Section)+1))) as CLA
from
    Bookings
order by
    CLA asc, Name_Level_Class_Section asc

So that when I perform the SQL, it will give me the following result:

==========================================================
|  Name_Level_Class_Section   |  Phone Num     |  CLA    |
==========================================================
|  Jacky_1_B2_23              |  1122554455    |  B2     |
|  Johnhy_1_B2_24             |  1122554455    |  B2     |
|  Peter_2_A5_3               |  1122554455    |  A5     |
==========================================================

Is there any way to simplify my SQL?

You can use cross apply and the third parameter of charindex to get the position of the underscores.

declare @T table
(
  Name_Level_Class_Section varchar(25)
)

insert into @T values
('Jacky_1_B2_23'),
('Johnhy_1_B2_24'),
('Peter_2_A5_3')

select substring(Name_Level_Class_Section, P2.Pos + 1, P3.Pos - P2.Pos - 1)
from @T
  cross apply (select (charindex('_', Name_Level_Class_Section))) as P1(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P2.Pos+1))) as P3(Pos)

Result:

-------------------------
B2
B2
A5

Update: Using your table the query would look like this:

select *,
       substring(Name_Level_Class_Section, P2.Pos + 1, P3.Pos - P2.Pos - 1) as CLA
from Bookings
  cross apply (select (charindex('_', Name_Level_Class_Section))) as P1(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P2.Pos+1))) as P3(Pos)
order by CLA asc,
         Name_Level_Class_Section asc

Update 2:

If you know for sure that your value never contains a period . and that it is always a four part name you can use parsename.

select *,
       parsename(replace(Name_Level_Class_Section, '_', '.'), 2) as CLA
from Bookings
order by CLA asc,
         Name_Level_Class_Section asc


Failed scheduled job I have created 3 jobs in my MS SQL Server

The job returns an error on 2. Remove top 4 lines

  Message
  Executed as user: myDomain\administrator. The process could not be created for step 2 of job 0xA849BBB21D41B64387336C2CAA7FB632 (reason: The system cannot find the file specified).  The step failed.

The job is a CMD script that works fine in CMD window

more +4 "D:\export\BRANCHES.XML" >"D:\export\BRANCHES2.XML"

Also, my third job is not working either. It wont change replace the text.

Here is the code

(Get-Content D:\export\BRANCHES2.XML) | Foreach-Object {$_ -replace '(1 rows(s) affected)','MyValue'}  | Out-File  D:\export\BRANCHES3.XML

Both the directory and the file exists on D:\export

I've got it working.
I first made a PS script in PowerShell ISE like this:

and then added it as a job in MSSQL


Is there any way to trace optimizer's work in MySQL? Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information like, which plans the optimizer considers, the cost of each plan?

The recent 5.6 version has added this feature.

See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer

Typical Usage:

# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";

SELECT ...; # your query here

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

# possibly more queries...

# When done with tracing, disable it:
SET optimizer_trace="enabled=off";

You can see it in action in SQL-Fiddle. Not sure how the output can be beautified.


Do mongodb's 2dsphere index works on multidimensional coordinantes array? if i create a index like that :

db.createCollection("users")
db.users.ensureIndex({coordinates:"2dsphere"});

and then insert a point

var point = {
   type: "point",
   coordinates: [ 1, 2, 3, 4 ]
}

db.users.insert(point)

and after execute the query :

db.users.find(
  {
     coordinates:
       { $near :
          {
            $geometry: { type: "Point",  coordinates: [1, 2, 3, 3 ] },

            $maxDistance: 5000
          }
       }
   }
)

it seems to work, does de the 2dsphere index works as expected with multidimensional coordinates ?.

The GeoJSON spec doesn't support multidimensional coordinate positions in the format you are suggesting:

  A position is represented by an array of numbers. There must be at least two elements, and may be more. The order of elements must follow x, y, z order (easting, northing, altitude for coordinates in a projected coordinate reference system, or longitude, latitude, altitude for coordinates in a geographic coordinate reference system). Any number of additional elements are allowed -- interpretation and meaning of additional elements is beyond the scope of this specification.

A 2dsphere index in MongoDB 3.0+ will allow additional values in the coordinate array but only the first two values will be indexed and used in a 2dsphere query. This allows GeoJSON objects with additional elements (eg. altitude) to be saved in MongoDB, but interpretation or use of those extra values is up to your application code.

The MongoDB manual has information on supported GeoJSON objects.

There's also a relevant feature suggestion you can upvote/watch in the MongoDB issue tracker: SERVER-691: n-dimensional geospatial search.


Something faster than UNION ALL? I have a view with two UNION ALL between three tables with three or more identical columns, but displaying different information. But I want to increase its performance. Is there an alternative more faster than UNION ALL? Thanks in advance.

Here is the actual execution plan of the view.

And the definition of the view

Based on the plan you posted: no.  Grabbing the info from the tables is 98% of your query time and actually concatenating them together is less than 1% so the UNION ALL is not actually taking up much of the query time.


Where Mysqldump gets data from How mysqldump is processing backup?
From which all files is it reads data for creating dump?
Is the process differs if storage engines are different?

mysqldump is a client application. It connects to the MySQL server using "normal" credentials and queries for data.

It has no immediate access to the data other than what it can ask of the server. The mysqldump binary doesn't have to run on the same host as the MySQL server. Hence it is obvious it cannot access files directly.

It is instructive to turn the general log on for the duration of the dump to understand how it works.

For example, you might see in the general log queries such as:

                   29 Query     SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
            WHERE SCHEMA_NAME = 'test'
                   29 Query     COMMIT
                   29 Query     SHOW CREATE TABLE `test`.`t1`
                   29 Query     COMMIT
                   29 Query     SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
            WHERE SCHEMA_NAME = 'test'
                   29 Query     COMMIT
                   29 Query     SHOW CREATE TABLE `test`.`t2`
                   29 Query     COMMIT
                   29 Query     SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'
                   29 Query     COMMIT

As you can see, mysqldump interrogates the schema via IFNORMATION_SCHEMA. It later follows to get all data via standard SELECT:

                6 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

So, mysqldump's power is by correctly iterating all objects and querying for data, then producing the SQL statements to generate those.

Since mysqldump accesses as any normal client (seen by the above SELECT), it does not care about the storage engine, other than caring about what to export as table's engine. It is up to the MySQL server to access the engine (via storage engine API) and fetch the data.


Postgres - how much space is required to perform a VACUUM I know that doing a VACUUM FULL on a table will free up disk space and return it to the OS.

There's been a couple of occasions where my OS (windows) has warned me that it's running low on disc space. My response has been to quickly shut down everything I can and run a VACUUM FULL on one of the more active tables.

I've noticed that VACUUM FULL appears to require a least a few hundred Mbytes free to actually work. If I attempt a VACCUM FULL on a 1Gbyte table when I've only got say 50 Mbytes of disk space then this quickly disappears and my computer grounds to a halt pretty quickly and the VACUUM FULL won't work.

How do I free OS space in a POSTGRES database when I know there is unused space available, but there's not enough space to run a VACUUM?

(I know the correct answer is - 'don't be stupid enough to let it happen in the first place', but sh1t happens occasionally!)

how much space is required to perform a VACUUM

At worst pg_total_relation_size(table) * 2. Usually lots less because the new table and indexes are significantly more compact.

  How do I free OS space in a POSTGRES database when I know there is unused space available, but there's not enough space to run a VACUUM?

Clean up unrelated files on the volume. Drop some tables or indexes you don't need. Move tables or indexes to other tablespaces on other storage. Expand the underlying volume.

There isn't really a good way to compact in-place and free space to the OS without first needing to allocate more space. I think most people agree this is a flaw, but nobody seems to care enough to develop any improvement for it.


Creating external tables from SQL Server using JDBC Drivers I am trying to query Hive tables AND Kylin tables from SQL Server 2019. I was doing some research and found that MSSQL provide PolyBase as a form of interface to Hadoop storage. However, the examples mostly include creating tables and storing file formats into HDFS. All I need is to create an external table from MSSQL using JDBC drivers. Is this possible? I would like to avoid using ODBC drivers.

All I need is to create an external table from MSSQL using JDBC drivers. Is this possible?

No.  The supported data sources are here:

Hadoop
Azure Blob Storage
SQL Server
Oracle
Teradata
MongoDB
ODBC Generic Types

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15


SQL Update column with another table column I'm using Postgres 9.4, and already seen others asking the same (simple) question, but theirs answers do not solved my problem and I can't see why. Suppose that I have these table A with common data and what type these data correspond, and consequently, a indicator (which I want to add that is in another table B):

 gid, type,  indicator
    1       a      ''
    2       b      ''
    3       e      ''
    4       a      ''
    5       d      ''

And this table B, which describes types and their respectives indicators

 type, indicator
    a       alfa
    b       alfa
    c       beta
    d       beta
    e       gama

I'm doing this query, but getting indicator=alfa for every row.

UPDATE A
SET    indicator = B.indicator
FROM   B
JOIN   A aa ON B.type = aa.type

The result that I was looking for A is something like this:

 gid, type,  indicator
    1       a      alfa
    2       b      alfa
    3       e      gama
    4       a      alfa
    5       d      beta

Hope that was clear

Update Syntax for PostgreSQL is different:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

In your case please try the below query:

UPDATE TableA AS A
SET indicator = B.indicator
FROM TableB AS B
WHERE B.type = A.type

Result:

gid     type    indicator
--------------------------
1       a       alfa
2       b       alfa
3       e       gama
4       a       alfa
5       d       beta

SQLFiddle working DEMO


How to use except but excluding a column from the except operator comparison -psql? I want to compare two sets of columns but exclude one column from the comparison as it will never be in the second table and I need it on my result.
So, explaining:

columns for both tables:

ID (unique value, so I do not want to compare)
amount
email
date

What I want to achieve:
Get all transactions from table 1 that has the exact same values on amount, email and date. I also want the ID to be in the final result set, but if I compare using the except clause, I will have 0 results at my query.

code:

Select /*ignoreAtComparison*/ t1.ID, t1.amount, t1.email, t1.date from t1

except

Select /*ignoreAtComparison*/ t2.ID, t2.amount, t2.email, t2.date from t2 ;

How can I do this?

except can be re-written as not exists query:

select t1.id, t1.amount, t1.email, t1.date
from t1
where not exists (select 1
                  from t2
                  where t2.amount = t1.amount
                    and t2.email = t1.email
                    and t2.date = t1.date);

But your statement seems to indicate you do not actually want except but something entirely different.


Transfer audit data using SSIS I want to transfer my audit data from an audit file to a table using SSIS in SQL Server 2012. I don`t have experience with SSIS, so I am asking for your help.

I have already created a table to store the audit data. I am choosing OLE DB Source as source, but what should be the destination ? There is Audit option in "Other Transforms", but how can I use it ?

The Audit Transformation in SSIS Data Flow 'Other Transforms' section is really just for adding data about the environment in which the package is running to the data flow, things like the package name, package id, computer name etc.

If you want to work with SQL Server Audit data that has been output to a file, you probably want an OLE DB Source running SQL similar to this, and some kind of management around which audit files you've already imported:

SELECT *
FROM fn_get_audit_file ( 'S:\SQLAudit\auditTableAccess_*.sqlaudit', DEFAULT, DEFAULT );

This article covers that process quite well:
https://www.mssqltips.com/sqlservertip/3444/automate-the-import-of-sql-server-audit-files-into-sql-server-using-powershell/


Connecting to Oracle Pluggable database using service name I installed Oracle 12c on windows using all the defaults but choosing to use a pluggable database and to install the sample data. The container database and SID name is ORCL and the pluggable database is PDBORCL.

I noticed that using SQL Developer I can connect to the pluggable database by using its name as the service name. Also I can connect to the container database using its name either as SID or service name.

Looking at my tnsnames.ora I found that only ORCL is mentioned as a service name. Does oracle register pluggable databases as service names automatically or there is a configuration file that I am missing?

It registers them automatically.

Very easy to test...

Listener status shows no PDB:

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:41:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-MAR-2015 15:03:10
Uptime                    8 days 18 hr. 38 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$ echo $ORACLE_SID
CDB1
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0

Create a new PDB:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 13 09:41:27 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

create pluggable database pdb_phil
admin user phil identified by phil
  3  file_name_convert = ('/pdbseed/', '/pdb_phil/');

Pluggable database created.

SQL> col name for a20
SQL> col open_mode for a10
SQL> select con_id, dbid, name, open_mode from v$pdbs;

    CON_ID       DBID NAME                 OPEN_MODE
---------- ---------- -------------------- ----------
         2 4096360259 PDB$SEED             READ ONLY
         3 3580684491 PDB_PHIL             MOUNTED

SQL> quit

It is now registered with the listener:

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:44:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-MAR-2015 15:03:10
Uptime                    8 days 18 hr. 40 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "pdb_phil" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$


Delete by primary key takes too long What would make a query that deletes a row using the primary key of the table .. take too long ?

This is a table with about 1.4M rows that gets .. 70%/30% read/write .

I don't know where to look.

Thanks.

EDIT

Engine: InnoDB
Columns: 30 - 40
Indexes:

SHOW INDEXES FROM produced this:

course_id           1   course_id   A   4516
complete_flag   1   complete_flag   A   16
pass_flag           1   pass_flag   A   16
reported_flag   1   reported_flag   A   16
student_id_2    1   student_id  A       50436
student_id_2    2   course_id   A       907849
student_id_2    3   complete_flag   A   907849
student_id_2    4   pass_flag   A       907849
reported_status 1   reported_status A   16
reported_status 2   reported_reason A   16
course_id_index 1   course_id   A       391
reported_flag   1   reported_flag   A   16

No Triggers

No Cascading

UPDATE

OK, so eventually things started getting worse and worse and after tuning some other queries  decided to restart the mysql server.

After restart the system needed to do a FSCK and after that it currently seems stable.

Thanks for the links in the answer, those will surely be very helpful in continuing to tune-up the database.

PS: the delete statement was a simple

delete from x_table where id = 1

Looking at your current information, I see you posted the following info from SHOW INDEXES FROM:

Key_name
Seq_in_index
Column_name
Collation
Cardinality

The indexes you have are

course_id
complete_flag
pass_flag
reported_flag
student_id_2
reported_status
course_id_index

Give the read/ratio of 70%/30%, running a DELETE against a Row in this InnoDB table is not as simple as one would think. Why?

InnoDB's Index structure and Locking. All secondary indexes in an InnoDB table have a key back to the Clustered Index (a.k.a. gen_clust_index). Therefore, index contention (intermittent deadlocking) would always be a problem. I have written past posts about this drawback

MySQL deadlock - cannot restart normally?
Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

If you are always having this issue with DELETE on a single row, you may want to think about applying either a tombstone table or adding a deleted flag to your table. I wrote a post about this as well: Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios