SQL Server Express: difference between connecting to localhost and localhost\sqlexpress I am new to SQL Server and am following some tutorials.

I have not yet created any databases. In SQL Server 2012 Management Studio I click on "connect" -> "Database Engine". I noticed that, using the server name field, I can type localhost and then connect or localhost\sqlexpress and it also connects successfully.

I don't understand what the difference is, and practically what the impact would be to using one over the other. Could somebody please provide an explanation?

In the image below you can see I am connected to both at the same time. One has databases and one does not. What is the difference between these two instances? Why does one have the databases I created and the other does not?

Hypothesis one: You have installed a named instance called SQLExpress. This instance was configured to listen on the default SQL Server port 1433. Therefore wether you connect to 'localhost' or to 'localhost\SQLExpress', you are connecting to the same instance.

Hypothesis two: You have installed two different SQL Server instances, one named SQLExpress and one default, unnamed. Both connections succeed, but you are not connecting to the same instance.


How can I programmatically grant (and revoke) temporary database access in MySQL? Suppose I have a datastore comprising many databases. For the purposes of this question, let's assume MySQL, but I suspect it might apply to many technologies. The stored records aren't particularly special. They're not credit card numbers, financial transactions, or any type of data covered by existing American law or particular compliance levels. They are, however, production.

From time to time, users, be they developers, data quality specialists, or support personnel, need access to the data store. Traditionally, credentials were handled with RO users, but that method is inherently insecure insofar as the passwords require frequent rotation, whenever someone leaves the company or the credentials simply age. Most times, users need temporary access -- an afternoon, day, or week at most. Temporary credentials are a perfect fit, but a nightmare to manage by hand.

How can I effectively grant and revoke temporary access to my datastore, preferably programmatically? I can envision any number of scripts that leverage, say, a highly privileged user to add and drop temporary users, but it all feels like reinventing a wheel that should already roll. Google results are surprisingly unhelpful here, making me think the problem is either too easy to discuss or too difficult to solve.

It depends upon how sophisticated you want to be with your user management.

In MySQL I would create a small selection of "temporary" users with no privileges, not even to log in. When a user requests access to some part of the database, I would have a script run GRANT statements to allow them access to the resources they need, and have a cron (or other scheduled) script to automatically run the corresponding REVOKE statements.

In a more sophisticated database (like Oracle), the user can be set to expire automatically after a set period of time. Once their time has expired, they are no longer allowed to log in until you (the DBA) re-enables their account.


Recompile not working for DELETE statement I want to optimize a deletion from my tables.

I have a table-variable input for my usp of following type:

CREATE TYPE [AddressInfoParts] AS TABLE(
(
    [TcpInfoId]     [BIGINT] NULL INDEX IXTcpInfoId CLUSTERED,
    [EmailInfoId]   [BIGINT] NULL INDEX IXEmailInfoId NONCLUSTERED,
    [PhoneInfoId]   [BIGINT] NULL INDEX IXPhoneInfoId NONCLUSTERED
)

Here I have a deletion:

DELETE FROM [doc].[TcpInfo]
WHERE ID in (SELECT aip.TcpInfoId FROM @AddressInfoParts aip)
OPTION(RECOMPILE)

With following plan:

My question is: why it expects for 1 row (in Clustered index seek), when I specify recompile (and expect, that it will recompile a procedure for last table variable size (2000 in this case)).

I always have a table of the same type, but I don't know how can I tell it to SQL Engine. Because it selects non-optimal plan: it uses loops becuase expects only 1 row when we have N rows there, there is 1-1 relationship between variable and those 3 tables. Each column has X unique values and Y NULLS. But in practice one of them is 0 (we have all nulls or we have all unique non-null values).

When I split table on 3 unique tables, statistics works better, but anyway it expects only 1 row, when receive N:

Please, advice.

why it expects for 1 row (in Clustered index seek), when I specify
  recompile

That is fine.

Somewhat confusingly the estimated rows on the inside of a nested loops join are per execution of the operator.

A seek into a primary key will indeed return 1 row (or 0 if the value doesn't exists at all).

In your case you have 2,000 seeks all returning 1 row and the actual rows reported is 2,000 so the estimated rows per execution was correct. You need to multiply estimated rows by estimated numbers of executions and compare that to the actual rows to see if there is any discrepancy.

Your first plan shows that the estimated number of executions of the inner side will in fact be under estimated though (at 45 not 2,000) as it estimates the stream aggregate on TcpInfoId will return 45 distinct values. This is because there are no column statistics on table variables that tell it that in fact the values are unique. OPTION (RECOMPILE) just allows it to take into account of the number of rows in the table variable it doesn't provide any information on column density.

If you were to change the column definition of TcpInfoId to [TcpInfoId] [BIGINT] NOT NULL PRIMARY KEY or [TcpInfoId] [BIGINT] NULL UNIQUE CLUSTERED (difference being the second one will allow a single NULL) then this will provide uniqueness information that can be used.

  Each column has X unique values and Y NULLS. But in practice one of
  them is 0 (we have all nulls or we have all unique non-null values).

If you are on 2016 you can use a unique filtered index

 CREATE TYPE [AddressInfoParts] AS TABLE
 (
 [TcpInfoId] [BIGINT] NULL INDEX IXTcpInfoId UNIQUE WHERE [TcpInfoId] IS NOT NULL,
 [EmailInfoId] [BIGINT] NULL INDEX IXEmailInfoId NONCLUSTERED,
 [PhoneInfoId] [BIGINT] NULL INDEX IXPhoneInfoId NONCLUSTERED
 )

If you are on a version that doesn't support filtered indexes in table types perhaps you can use a #Temporary table instead (as they can get column statistics created).

Or you can stop trying to use a single table type for these two very different cases.

If I understand your question correctly this is what the second plan represents. The thing you point out as a problem there is not in fact a problem as explained at the beginning of this answer.


MySQL CHAR_LENGTH() alternative in IBM DB2 I have a value in a cell that has a Unicode character:

"Nit€sh"

I want to get the length in SB2.
The problem is, If I use the length function it's considering '€' as 2 and total is 7. I am trying to achieve a total of 6 in this case.

EXAMPLE
In MySQL we had char_length() to address this.

Is there anything similar in DB2?

This page explains how to deal with string lengths when Unicode characters are involved.

CHARACTER_LENGTH is the function you need.

CHARACTER_LENGTH(yourstring,CODEUNITS32);


Is a update-only-once-row table worth sharding? I have a big MySQL 5.6 Inno DB table that contains user's request.
A table row updates only once, shortly-after the initial insert!
(The initial insert is to get the unique auto-incrementing request_id in order to do the processing of the request)

CREATE TABLE requests (
id bigint(20) NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
request text
data text,
) ENGINE InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

psuedo script code goes like this:

execute("INSERT INTO requests (id) VALUES (default)")
my $id = query("SELECT LAST_INSERT_ID()")
my $data = do_some_fast_processing($id, $query)
execute(qq# UPDATE request SET query="$query", data=$data WHERE id=$id #)

There are roughly 10 million request per day. Meaning 10 million instance of  "insert-and-then-update".
Assume I shard this into 2(odd/even user id), And assume both shards will do half of each day's request, would I gain a significant/worthy write performances? because of  the lack of locking? Also assume my machine IO isn't maxed.

Is there a problem?  Or are you expecting to grow significantly?  10M/day = 120/second, which is high, but not necessarily the limit.

innodb_flush_log_at_trx_commit = 1 is the safest, but it is the slowest.  A value of 2 will give you a boost in performance.

Batching INSERTs is also a performance boost; however you may not be able to do so because of how your app works.  Also combining statements into a transaction would help -- if practical.

What you have described of your app sounds like all the activity is concentrated near the 'end' of the one table.  This implies that there is very little I/O (other than transaction logging, and that was what I was addressing above).

Back to your question...

Sharding across N servers will cut the I/O, locking, CPU, etc by a factor of nearly N.

The process that decides which shard to go to should be another machine, and the shards should be alone on each of the machines.

A problem with sharding to handle growth is what to do when you need more than N machines.


MS Migration Assistant (SSMA) index renamed I am using Microsoft SQL Server Migration Assistant v6 (aka SSMA) to update a database from Access to SQL Server.  I am getting informational messages that say "A2SS0029: Index name 'Name' was changed during conversion.".

As these are just index names, that shouldn't cause a problem for me.  But I do wonder why the name has to be changed.  Particularrly since I was able to create an index with the given name manually after the migration.

Why are the index names being renamed? And am I wrong about that not causing any problems (given that I don't have any queries that use index hints, if that is even possible with Access).

While there are reasons that a tool might make intelligent decisions about index names (like to prevent duplicate names in cases where there are actual collisions, and further when it actually matters, like when they are in the same table), it would appear that SSMA just creates new, unique names for all indexes. Perhaps only in the case where it finds at least one index name that is reused, not sure. I would test this, but I don't have Access installed anywhere.

Why does it do that? Who knows? Extreme conservatism? Paranoia? I think it's just being extremely safe.

There are cases where duplicate names would be a problem, but these are isolated to first class entities that end up in sys.objects, like primary key constraints. Index names are only enforced to be unique at the table level (since the name is only exposed in sys.indexes), not at the database level.


Compare 2 dates using a CASE in WHERE I want to compare 2 dates and use CASE in WHERE to implement this logic:
if the end date is larger than 2016-06-30, the end date should be 6/30/2016.

However, I'm encountering an error. The error message is

  Missing keyword

My code is below:

    select start_date, end_date, amount from info
    where
       case end_date
       when to_char(end_date, 'yyyy-mm-dd') > '2016-06-30' then to_date(to_char('2016-06-30'), 'M/D/YYYY') as end_date
    end
    order by end_date asc

Can you help me?

You could try the following to achieve the result. Your date format string is invalid and the column alias should be placed after the END clause of CASE expression.

SQL> desc info
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 START_DATE                                         DATE
 END_DATE                                           DATE
 AMOUNT                                             NUMBER

SQL> select * from info;

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 14-SEP-16       1200
14-SEP-16 30-JUN-16       1300
30-MAY-16 30-JUN-16       1300
14-SEP-16 20-JUL-16       1400

SQL> select start_date,
case
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06-30-2016', 'MM/DD/YYYY') end as end_date, amount from info
order by end_date asc;  2    3    4

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16       1200
14-SEP-16 30-JUN-16       1400
30-MAY-16                 1300
14-SEP-16                 1300

OR

SQL> select start_date,
case
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06/30/2016', 'MM/DD/YYYY') else end_date end as end_date, amount from info
order by end_date asc;  2    3    4

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16       1200
14-SEP-16 30-JUN-16       1400
30-MAY-16 30-JUN-16       1300
14-SEP-16 30-JUN-16       1300

SQL>


How to install columnstore in MariaDB on Mac OS X with Homebrew I would analyse time-series data using MariaDB ColumnStore but error encountered

MariaDB [(none)]> CREATE DATABASE forex;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE forex;
Database changed
MariaDB [forex]> CREATE TABLE gbpusd (
    ->     id INT,
    ->     time DATETIME,
    ->     open DOUBLE,
    ->     high DOUBLE,
    ->     low DOUBLE,
    ->     close DOUBLE
    ->     ) engine=ColumnStore default character set=utf8;
ERROR 1286 (42000): Unknown storage engine 'ColumnStore'

brew install mariadb

After checking, I found the columnstore engine didn't exist.

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| MyISAM             | YES     | MyISAM storage engine                                                            | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

Please advise how to enable columnstore in MariaDB

MariaDB currently is a distinct download from regular vanilla which adds in the columnstore engine: https://mariadb.com/downloads/mariadb-ax

However we don't currently have a native Mac build so the simplest recommendation is to run this via docker in a vm: https://mariadb.com/kb/en/library/mariadb-columnstore-docker-vagrant-and-windows-10-linux-setup-allows-for-ev/


Populate Identity column with sequential numbers restarting count on change of key field I am attempting to populate an Identity column with sequential numbers but I want the count to start over when the value of a key field changes.

Example data and desired ID column results:

Month_  Date_   Year_   SalesRep  ID
10  2018-10-31  2018    AL01      0
10  2018-10-31  2018    AL01      1
10  2018-10-31  2018    AL01      2
10  2018-10-31  2018    AL01      3
10  2018-10-31  2018    AL02      0
10  2018-10-31  2018    AL02      1
10  2018-10-31  2018    AL02      2
10  2018-10-31  2018    AL02      3

The below query does not produce the desired results:

INSERT INTO SALES_INFO_SNAPSHOT_WD_TEST(ID)
SELECT DEL_ID
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Year_,Month_,Date_,SalesRep ORDER BY
SalesRep) - 1 As DEL_ID

FROM
Sales_Info_Snapshot_WD_TEST
where date_ = '10/31/2018') D
WHERE DEL_ID IS NOT NULL

Anyone know of a painless solution?

The select query works perfectly but does not insert into the table.
I need to number these rows in order to identify a large chunk of data that needs to be deleted from a very important reporting table.

Thank you.  Any help is appreciated.

Marlene
SSMS v 17.7

So it was suggested that I post the answer I was able to come up with to resolve my issue.  I decided to create a new table to make sure it would work and then save that table as the original table.

SELECT
*, ROW_NUMBER() OVER(PARTITION BY YEAR_,MONTH_,DATE_,SALESREP
ORDER BY   SALESREP)-1 AS DEL_ID INTO

SALES_INFO_SNAPSHOT_TEMP FROM SALES_INFO_SNAPSHOT_TEST
WHERE DATE_ = '10/31/2018'

This did exactly what I wanted.  A column with sequential numbers beginning with 0 and was inserted with existing data into a new table. The sequential numbers restarted at each change in year_,month_,date_,salesrep.

This allowed me to determine which of four duplicate rows per SalesRep to be deleted.  There were over a thousand rows in all for date_ = '10/31/2018' so I wanted to get this right the first time.
So I was able to determine which row to salvage (the same for each SalesRep) and send a DELETE statement.  It was determined to salvage where ID = '1'.

DELETE FROM SALES_INFO_SNAPSHOT_TEMP WHERE DATE_ = '10/31/2018' AND ID IN ('0','2','3')

Worked like a charm :)


If a query reads more pages than the InnoDB buffer can hold, are all the previous pages evicted? If I have some (hot) pages in the InnoDB buffer, and I perform a query which reads an amount of data larger than the buffer itself, will the hot pages be necessarily evicted?

According to MySQL 5.7 Reference Manual - The InnoDB Buffer Pool

InnoDB manages the buffer pool as a list, using a variation of the least recently used (LRU) algorithm. When room is needed to add a new page to the pool, InnoDB evicts the least recently used page and adds the new page to the middle of the list. This “midpoint insertion strategy” treats the list as two sublists:

At the head, a sublist of “new” (or “young”) pages that were accessed recently.

At the tail, a sublist of “old” pages that were accessed less recently.

This algorithm keeps pages that are heavily used by queries in the new sublist. The old sublist contains less-used pages; these pages are candidates for eviction.

(emphasis mine)
I think this is the answer you're looking for. Not all pages are evicted, they're evicted one at at a time, and only of the old ones will be evicted each time. Obviously, if queries need twice (or more) of the available pool, eventually all pages might be evicted, depending on how frequently they're used.
I'd say that all pages near the root of B+trees (used for indices and the way tables are clustered) will be used more frequently than the rest, and so, they're unlikely to ever be evicted. However, some situations where there are just lots of full table scans, that (probably) pass through pages just once, might end up having all your hot pages overwhelmed and evicted. I don't think this is likely, but theoretically possible.


Full outer join in MySql I'm on MySQL trying to get the user's daily post count grouped by post category.

So for every day in a given date range, with no gap, I need the post count for each existing post category for a given user. When the user has not posted anything in a category for that day, that category must still be listed with a null or, better yet, 0 postCount.

I've solved the daily date part by using a sequence table. But I'm failing to include the empty categories. I believe I need to emulate full outer join with union, but I'm not sure where to start.

My current query:

select
    `list_of_dates`.`date` as `date`,
    `post_categories`.`name` as `categoryName`,
    count(`posts`.`id`) as postCount
from (
    SELECT
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date
    FROM
        seq_0_to_999 AS seq
    WHERE
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28'
) as list_of_dates
left join
    (
        SELECT `posts`.* FROM posts WHERE `posts`.user_id = 65
    ) as posts on DATE(`posts`.`created_at`) = `date`
left join
    `post_categories` on `post_categories`.`id` = `posts`.`category_id`
group by
    `date`, `categoryName`
order by
    `date` asc

You need to first CROSS JOIN the two tables, list_of_dates and post_categories and then LEFT JOIN posts:

SELECT
    ld.date     AS date,
    pc.name     AS categoryName,
    COUNT(p.id) AS postCount
FROM
    ( SELECT
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date
      FROM
          seq_0_to_999 AS seq
      WHERE
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28'
    ) AS ld                   -- list_of_dates
CROSS JOIN
    post_categories AS pc     -- list_of_categories
LEFT JOIN
    posts AS p
  ON  p.user_id = 65
  AND p.created_at >= ld.date AND p.created_at < ld.date + INTERVAL 1 DAY
  AND p.category_id = pc.id
GROUP BY
    ld.date,
    pc.id, pc.name
ORDER BY
    ld.date ASC ;


SQL Server Availability Groups between virtual server and a bare metal sql server I currently have Mirroring set up between two production SQL Servers. One of these servers is a hardware SQL server (principal) and the mirror is a VM (on different server). I need to migrate these servers to Availability Groups (HA cluster NOT using shared storage).

None of this is my choice. I would personally prefer two VMs, or completely identical systems.

My question is: has anyone come across issues for AG/clustering between a hardware SQL Server and a VM SQL Server? Or is there any issues that you can imagine with that setup?

I have had no issues with Mirroring between a hardware & VM. As long as the servers are very similar (ie: same version of SQL Server, same RAM given, data files are named & stored in the same locations, etc). My concern is AG utilizes windows failover clustering - an unfamiliar territory for me - so I'd like to know if there's anything additional to look out for.

I have had no issues with Mirroring between a hardware & VM. As long as the servers are very similar (ie: same version of SQL Server, same RAM given, data files are named & stored in the same locations, etc).

Then it should be exactly the same for Availability Groups. You shouldn't see a difference.

  My concern is AG utilizes windows failover clustering - an unfamiliar territory for me - so I'd like to know if there's anything additional to look out for.

The biggest thing is quorum. In mirroring there was the option to use a witness instance for HA which sort of but not really acted like quorum. I say sort of because it didn't really vote because there could only ever be two instances in mirroring. Availability Groups rely on WSFC (except read-scale AGs, though they can have an optional metadata instance - and anything on Linux) which means there will be voting to determine quorum.

Here is a small introductory, there is more out there but this should give you an idea what you'll want to research.


Speed up SQL NOT IN query MySQL I have a query as part of my process, that I execute hundreds of times in a loop.

Initially, Table A contains all records (20mil).  Table B contains 0 records.
Primary key in both tables ID
the I execute:

select * from table A where a.ID not in (select ID from table b) limit 10000
##magic stuff in python
insert everything to table B, once again, .

Initially, the query runs super fast, but after Nth loop (100th+), size of table B increases, to the point where I it takes a bit of time to perform the NOT IN operation.

Does anyone have recommendations on how I can speed up the query?
- So far, I've tweaked the default mysql bugger to be 1.5gbs (ids are pretty small INTs, so that should be enough).

Caveats:

1) One way to do this would be to remove * from table A after I've processed them.  However, I want to keep table A in tact.

... only method I could think of I adding another column to table A (which I'd index) called 'PROCESSED'... then update that column with a second query once the records have been processed/posted, but I was hoping there was an easier solution.

Thank you all in advance.

If your goal is to look at every row in A and do something with it, there is a much more efficient way.  (It seems that B is merely there to see what you have already processed.)

The reason for it getting slower is that it has to do more work as it gets farther into A -- namely skipping over the rows it has processed.  A processed flag might suffer the same malady.

So...

Walk through A processing chunks as you go.  Then remember where you left off so that the next 10000 will be right there, no searching.  I discuss that in more detail with an eye to DELETEing, but it can be adapted for other purposes:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks  With that, B is unnecessary.

Partitioning

If you are thinking about partitioning the data by months, I have to ask you "Why?".  Here are some answers:

For performance?  You won't get any.
For rapid deletion of "old" data?  This is a good use case, but be sure to use PARTITION BY RANGE(...) and include the year, too.  More:  http://mysql.rjweb.org/doc.php/partitionmaint


Mysql connection pool set timezone Service application is nodejs It is using mysql connection pool.

Currently, we will plan to sell service to another country What is the best way to set timezone?

timezone setting execute, and query execute? Like "Set timezone='UTC'; SELECT * FROM user;"

To setup the default Time Zone, you can edit the global variable system_time_zone (SET GLOBAL). You most likely want to change that on the configuration file so it persists after a reboot.

Timezones can be numerical or can use alias if setup properly.

To setup a client custom timezone, the right way is to SET SESSION time_zone ='<your timezone>';

Details at https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

Most connection pools will destroy or cleanup the connection after usage and reset to the default, in any case, if they can vary per client, you want to set those up after each connection by running the SET SESSION command above.

Having said that, I would strongly recommend to handle timezones on application (presentation) layer and work only in a single timezone on server side (on UTC, preferably, or the local timezone only). While MySQL support is not bad, it can be confusing when handling multiple timezones (a 23:59:60 second, daylight saving changes, etc.). It may take a hard one-time conversion but it will be worth in the long term.


Improving Backup Performance using SQL native methodology I am trying to gather some stats on comparison of run time vs performance between Litespeed backups and sql native backups. As we are no longer going to use Lite Speed [even though i personally loved that SW], have to gather metrics how SQL will perform using native compression backup]

So i am looking for a way to reduce CPU usage to X % while taking native compression backups. In Litespeed we just specified this option in advanced tabs and saw everything running fine. I am aware of resource governor, but due to some constraints not able to use it. Will need to work on that. But is there a better and smarter way to do this. Please suggest

Edit.. Have to add, as per security and company policies we cannot use third party or external SP's to be created. I saw this can be achieved using OLa 's script as well, but we wont get approvals to introduce this :(

Resource Governor can control the resource consumption of a backup.

By default, backing up using compression significantly increases CPU
usage, and the additional CPU consumed by the compression process can
adversely impact concurrent operations. Therefore, you might want to
create a low-priority compressed backup in a session whose CPU usage
is limited byResource Governor when CPU contention occurs. This topic
presents a scenario that classifies the sessions of a particular SQL
Server user by mapping them to a Resource Governor workload group that
limits CPU usage in such cases.

Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)


Index for date part of field? I have a datetime field, but I often query over the date part, like this

select * from table where date(datetime_field)=curdate()

This query can't use  index on datetime_field!
What would be the best solution with the minimum amount of changes in code or db?

PS. upgrading mysql could be an option if there is a solution I'm not aware of yet, but a more short term solution for 5.6 would be appreciated

The problem isn't the index.  The problem is that your query as written does not allow an index to be used.

Modify your query so that the date range is resolved to a pair of constants and the problem is solved:

WHERE datetime_field >= CURDATE()
  AND datetime_field < DATE_ADD(CURDATE(), INTERVAL 1 DAY)

This will use the index on the column.

As a rule, never use a column as an argument to a function in WHERE because this forces a table scan.


Missing Index finding queries I just need a reliable Script by which I can get all the missing index report.

I found many but they don't want to work.

Check out Brent Ozar's stored procedure called sp_BlitzIndex.
You will find a lot of usefull applications of it.


How to improve InnoDB DELETE performance? So I have this audit table (tracks actions on any table in my database):

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`),
  KEY `actionDate` (`action`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and I need to start archiving outdated items. The table has grown to about 50million rows, so the fastest way I could delete the rows was to delete it a table at a time (based on tableName).

This works pretty well but on some of the tables that are write-heavy, it won't complete. My query deletes all items that have an associated delete action on a tupleID/tableName combination:

DELETE FROM track_table WHERE tableName='someTable' AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableName='someTable' AND action='DELETE' AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
)

I let this run on my server for 3 days and it never completed for the largest table. The explain output (if I switch the delete to select:

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

So 4 million rows shouldn't take 3 days to delete, I would think. I have my innodb_buffer_pool_size set to 3GB, and the server is not set to use one_file_per_table. What other ways can I improve InnoDB delete performance? (Running MySQL 5.1.43 on Mac OSX)

You could delete data in batches.

In SQL Server, the syntax is delete top X rows from a table. You then do it in a loop, with a transaction for each batch (if you have more than one statement, of course), so to keep transactions short and maintain locks only for short periods.

In MySQL syntax: DELETE FROM userTable LIMIT 1000

There are restrictions on that (can't use LIMIT in deletes with joins, for instance) but in this case you might be able to do it that way.

There is an additional danger to using LIMIT with DELETE when it comes to replication; the rows deleted are sometimes not deleted in the same order on the slave as it was deleted on the master.


What's default path for "db2move export" command's output What's default path for "db2move export" command's output in Linux RedHat 7.2 and how to change it to specific folder?

You can specify the path(s) for lob and xml columns with the -l parameter of the db2move utility only.
Other files will be created in the current directory and this can't be changed.


In what circumstances I am more likely to benefit from auto update stats async on? I update statistics on a weekly basis using Ola Hallengren's solution.

Based on the article below, I am considering enabling the Auto Stats Async
and turning on trace flag 2371.

Databases With Large Tables Should Use Auto Update Stats Async Feature

Right now, the async option is false for my database:

Also, from Important Hotfix for SQL Server 2008 and Newer:

  This setting reduces unpredictable query performance by allowing
  statistics to be automatically updated asynchronously while your
  currently running queries continue to use the old statistics until the
  updated statistics are ready to use. The alternative to this (which is
  the default) is for query execution to halt (only for queries that use
  statistics for that object) while the statistics are automatically
  updated synchronously for that object. Depending on the size of the
  object, and on your hardware and I/O subsystem, this could take
  anywhere from a few seconds to many minutes.

Question:

In a test environment, what could be a good and simple test to do before/after setting Auto Update Stats Async to ON?

In what circumstances I am more likely to benefit from auto update stats async on?

Databases 500GB+, big tables.

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards.

Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats (or users can't wait long enough for the stats update to happen and the query to run). The threshold will not be easy for anyone else to tell you - it depends on how long the stats update takes (so depends on data size, I/O subsystem, sample size, etc.), how much the stats update helps the query (it may or may not generate a different plan, for example), and we don't know your end users' definition of "this query is taking too long."

Again, generally, sync is beneficial when you know that stats go south quickly, and that any query that runs with stale statistics will suffer. In this case, you should have a good idea that the duration of the stats update + the time of the improved query is on average less than the duration of the query using the stale stats.


ORA-03113 when starting up an Oracle 11g database I have read "ORA-03113: end-of-file on communication channel" on startup and others from this site to solve my problem but I still have not solved it. All I get is the following:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jun 28 17:28:20 2014

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
SQL> alter system set db_recovery_file_dest_size=35G scope=both;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
Database opened.
SQL> select instance_name from v$instace;
select instance_name from v$instace
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 43880
Session ID: 170 Serial number: 5

SQL>

Environment: Windows 7 64 bit, Oracle 11g.

Here are the details from alerts.log:

Sat Jun 28 17:32:26 2014
Flush retried for xcb 0x8a6711a8, pmd 0x8afba968
Doing block recovery for file 3 block 2686
Block recovery from logseq 13, block 68 to scn 132386303
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
  Mem# 0: E:\APP\TRUCNGUYEN\ORADATA\XDLL\REDO01.LOG
Block recovery completed at rba 13.69.16, scn 0.132386305
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc  (incident=36103):
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc:
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
PMON (ospid: 55932): terminating the instance due to error 472
Sat Jun 28 17:32:27 2014
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_q001_79168.trc:
ORA-00472: PMON  process terminated with error
ORA-1092 : opidrv aborting process Q001 ospid (2836_79168)
Sat Jun 28 17:32:27 2014
ORA-1092 : opidrv aborting process W000 ospid (2836_56844)
Sat Jun 28 17:32:29 2014
ORA-472 : opidrv aborting process S000 ospid (2836_85052)
Instance terminated by PMON, pid = 55932

I'm a newbie in Oracle. I would really appreciate any direction and especially step by step instructions to fix this.

I have run the commands:

recover database;
alter database open;

but it still has the same error when I run:

select instance_name from v$instance;

From
ParnassusData:

  This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

  ARGUMENTS:

  Arg [a] - Maximum Undo record number in Undo block

  Arg [b]  -  Undo record number from Redo block

  Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

  Oracle is unable to roll back an uncommitted transaction in its transaction table. You probably have corruption in either the rollback/undo segment or corruption in the object in which the rollback/undo segment is trying to apply the undo record.

  Refer to Note: 39283.1 for more details on the description of this error.

Oracle Note  39283.1 says more or less the same.

Here is a link where somebody solved the problem by creating a new  undo tablespace: SAP, Databases and other IT stuff

Here are the statements:

-- Creation of a new Undo tablespace:
CREATE UNDO TABLESPACE undotbs
DATAFILE 'c:\oracle\10201\dev\undotbs1.dbf'
SIZE 300M AUTOEXTEND ON NEXT 20M
MAXSIZE 800M;

-- Change the default undo tablespace to the new one
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs SCOPE=BOTH;

-- Drop the problematic undo tablespace
DROP TABLESPACE oldundotbs;

To create a tablespace  the database must be open.

I don't know how to reproduce this error so I cannot test how to repair the system. I also think I won't get any feedback from the OP. But nevertheless let's think about, how we can proceed.

The best is to open a service request at Oracle Support.
One possibility is to restore the undo tablespace. But maybe the error will occur again.
Then a point in time restore of the whole database will be an option. But this means loss of data.
We will try to repair the system by creating a new undo tablespace an dropping the old one as described above.

To repair the system we unset the UNDO_TABLESPACE parameter. Then the SYSTEM tablespace becomes the default tablespace and I assume the database can be opened now.

startup nomount
alter system reset undo_tablespace scope=spfile;
shutdown
startup

Now we can proceed as above and create and set a new undo tablespace an drop the problem tablespace as described above.


How is this SQL Server PK violation possible? I'm getting an error that I thought was impossible. I have a SQL Server (ver 11.0.6594) table that looks like this:

CREATE TABLE [IDMaster](
  [ID] [int] NOT NULL,
 CONSTRAINT [PK_IDMaster] PRIMARY KEY CLUSTERED
([ID] ASC) ON [PRIMARY]
) ON [PRIMARY]

It's basically supposed to do what the IDENTITY does now (it's been around a while) – it is used to get new unique integer values, that are used in a few other tables.

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table – all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it is getting one.

  Violation of PRIMARY KEY constraint 'PK_IDMaster'. Cannot insert duplicate key in object 'IDMaster'. The duplicate key value is (25309587).

I don't know how this is possible, but apparently it is; because it just happened.

Imagine any crazy, odd mixture of other ways to try generating a new ID value from this same table, and they probably aren't far off from what exists in this system. But I can't think of any way that they could cause that particular error. It was my understanding that the transaction locking used by SQL Server enforces that, within one SQL statement that doesn't have (NOLOCK) on it or anything, there can't be any interference with the rows that it is referencing.

The only other factor, which I don't think could be related but who knows, is that this SQL is wrapped in a TRY, with some options set. Here's the SQL that got that error:

SET XACT_ABORT ON;
SET IMPLICIT_TRANSACTIONS ON;
BEGIN TRY

INSERT INTO IDMaster (ID)
 SELECT COALESCE(
  -- make sure the new value is odd
  CASE WHEN MAX(COALESCE(ID,0)) % 2 = 0
   THEN MAX(COALESCE(ID,0)) + 1
   ELSE MAX(COALESCE(ID,0)) + 2
   END,1)
 FROM IDMaster;

END TRY
BEGIN CATCH
  WHILE (@@TRANCOUNT > 0) ROLLBACK;
  THROW;
END CATCH
WHILE (@@TRANCOUNT > 0) COMMIT;

Please explain:

How is it possible to get that error?
If possible, how can I prevent it from ever happening (without altering the table)?

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table -- all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it IS getting one.

It definitely IS possible. This is due to your isolation level and how locking works.

  Violation of PRIMARY KEY constraint 'PK_IDMaster'. Cannot insert duplicate key in object 'IDMaster'. The duplicate key value is (25309587).

This is due to locking; initially in the read committed isolation level will take a shared lock on the (in this case) key while reading what the maximum value is. After it figures it out, an X lock will be needed to insert into the table. That's the simple overview of it.

When you have a single session, this isn't a problem. When you have multiple sessions this is a huge problem as shared locks are compatible with each other. This means that multiple readers can obtain the same value.

  don't know how this is possible, but apparently it is; because it just happened.

See above.

So How Do You Fix It?

Don't try to be smarter than SQL Server. Use the available constructs that fit the need - in SQL Server 2012 (major version 11) has both identity and sequences. Use them.
Purposely kill your concurrency by either using serializable or lock hints such as XLOCK. This will on purpose block other sessions which means you'll have less performance... and you're doing it on purpose... so yeah that seems bad but a possible solution.
Pre-create values (again trying to be smarter than SQL Server) and use XLOCK + READPAST to get a little better concurrency from a different table. Blah. Let me reiterate #1, don't try to be smarter than SQL Server [in this case].


You have mixed the REGEXP syntax for alternatives (first|second) and for symbol classes [abcd]. Your query should look like that:

SELECT *
  FROM tbl_json
 WHERE json_data REGEXP '"14":"(1|2)"'
   AND json_data REGEXP '"16":"(39|7)"'
 LIMIT 3;


Find out beforehand how many records a query has I develop reports in ExtJS resulting of queries in SQL Server 2012.

I use Ext.Grid with paging, therefore I can use offset to limit the amount of records a query will return. But for this ExtJS feature to work, I still must provide it the total amount of records the giving query has.

Some reports use filters and data changes during day, because of that I can only know the total count by running the query. This results in running it twice: once with count(*) to get total count and again to get proper data.

Is there a way to find the total count of a query, even when it has offset command, without having to run it twice as I'm doing now, and without looping through all records?

Unfortunately, the total number of records is not known until the query completes. This means that somehow you need to let the unrestricted query finish, and then grab the subset of rows.

One way to do this is to add COUNT(*) OVER () AS [TotalRows] to the SELECT list. This will return the value as a column with the same value repeated for every row, but still.

SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET 10 ROWS FETCH NEXT 7 ROWS ONLY;

Of course, that is not simply accessing a value that is freely available. Just compare th execution plan of the query above with the plan for the same thing without the COUNT(*).. expression:

SELECT [object_id], [schema_id], [name], [type_desc]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET 10 ROWS FETCH NEXT 7 ROWS ONLY;

Another method I come up with is described in detail in my answer to a nearly identical question on Stack Overflow:

TSQL: Is there a way to limit the rows returned and count the total that would have been returned without the limit (without adding it to every row)?

The difference between that question and this one is that the other question specifically did not want to return the value as a column in the result set it was counting. Also, the other question was more concerned with the "limit" aspect and this one is concerned with at least "offset" if not both. But adding the "offset" part isn't difficult: just add a loop prior to the loop that returns the desired results to do something like the following:

// assume an input param or variable of: int Offset;

int _RowCounter = 0;
while (Reader.Read() && ++_RowCounter < Offset);

Just for fun, I thought I would experiment with another mechanism that that can run a query and get the rowcount without retrieving the rows: a CURSOR (yes, the E-V-I-L CURSOR). The variable @@CURSOR_ROWS is populated after calling OPEN, even if you never FETCH any rows.

In the following (which I have no idea how well it performs), I make use of FETCH ABSOLUTE x to handle the "Offset" aspect since there is no need to cycle through fetching those initial rows just to not use them. But using ABSOLUTE disallows using options such as FAST_FORWARD and FORWARD_ONLY. Not being able to use FAST_FORWARD, I then used STATIC, but it would have to be tested to see if adding in STATIC actually helped or hurt.

Now, the only way to get the desired results is to store them in a table variable (or temp table would work but I think a table variable is better in this case) so they can be returned as a single result set. Doing FETCH without the INTO clause will return a different result set per call (most likely undesirable).

DECLARE @Offset INT = 10,
        @Limit INT = 7;

DECLARE cursed CURSOR LOCAL READ_ONLY STATIC -- can't use FAST_FORWARD or FORWARD_ONLY :(
FOR  SELECT so.[object_id], so.[schema_id], so.name, so.type_desc
     FROM   sys.objects so
     ORDER BY so.[object_id] ASC;

DECLARE @object_id INT,
        @schema_id INT,
        @name sysname,
        @type_desc NVARCHAR(60);

DECLARE @RowCounter INT = 0,
        @StartRow INT = (@Offset + 1); -- Offset is how many rows to skip

DECLARE @Results TABLE ([object_id] INT NOT NULL, [schema_id] INT NOT NULL,
                        [name] sysname NOT NULL, [type_desc] NVARCHAR(60) NOT NULL);

OPEN cursed; -- execute the query

SELECT @@CURSOR_ROWS AS [RowCountBeforeRetrievingAnyRows];

FETCH ABSOLUTE @StartRow -- position cursor at beginning of desired range
FROM  cursed
INTO  @object_id, @schema_id, @name, @type_desc;

WHILE (@@FETCH_STATUS = 0 AND @RowCounter < @Limit)
BEGIN
  INSERT INTO @Results ([object_id], [schema_id], [name], [type_desc])
  VALUES (@object_id, @schema_id, @name, @type_desc);

  SET @RowCounter += 1;

  FETCH NEXT
  FROM  cursed
  INTO  @object_id, @schema_id, @name, @type_desc;
END;

CLOSE cursed;
DEALLOCATE cursed;

-- return desired range of rows
SELECT * FROM @Results;

-----------------------------------------------
-- check the actual data to see if it worked
SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;

Considering that a STATIC CURSOR saves its results into tempdb, the following is essentially the same operationally, but a bit more direct:

DECLARE @Offset INT = 10,
        @Limit INT = 7;

DECLARE @Results TABLE ([object_id] INT NOT NULL, [schema_id] INT NOT NULL,
                        [name] sysname NOT NULL, [type_desc] NVARCHAR(60) NOT NULL,
                        [RowID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY);

INSERT INTO @Results ([object_id], [schema_id], [name], [type_desc])
  SELECT so.[object_id], so.[schema_id], so.name, so.type_desc
  FROM   sys.objects so
  ORDER BY so.[object_id] ASC;

SELECT @@ROWCOUNT AS [TotalRows];

-- return desired range via OFFSET / FETCH (Clustered Index Scan)
SELECT [object_id], [schema_id], [name], [type_desc]
FROM @Results
ORDER BY [RowID] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;

-- return desired range via WHERE (Clustered Index Seek)
SELECT [object_id], [schema_id], [name], [type_desc]
FROM @Results
WHERE [RowID] > @Offset
AND   [RowID] <= (@Offset + @Limit)
ORDER BY [RowID] ASC;

-----------------------------------------------
-- check the actual data to see if it worked. OR, maybe this is actually better?
SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;


How does MySQL determine the 'index first key' and 'index last key' with indexes? In other words, how does MySQL find the result range with indexes on the first step when it's executing a select query?

That is really the job of the MySQL Query Optimiser.  You can determine more information by evaluating the results of EXPLAIN.

You can also manually tweak the Query Optimiser by forcing or hinting an index, or changing the optimizer_search_depth variable which will tell the Query Optimiser how far in the future of each incomplete plan the optimizer should look to evaluate whether it should be expanded.


User SYS logged on from. Where I can find source of access? I´m receiving alerts from EM.

Host=Teste.com
Target type=Database Instance
Target name=REF.Teste.com
Categories=Security
Message=User SYS logged on from kali.
Severity=Warning
Event reported time=Mar 8, 2017 10:11:04 AM BRT
Target Lifecycle Status=Production
Comment=TI DEV-DV
Operating System=Linux
Platform=x86_64
Event Type=Metric Alert
Event name=UserAudit:username
Metric Group=User Audit
Metric=Audited User
Metric value=SYS
Key Value=SYS_kali
Key Column 1=Audited User - Host
Rule Name=Unavailability,Events
Rule Owner=SYSMAN
Update Details:
User SYS logged on from kali.

Where I can find the logs of this audit user?
"User SYS logged on from kali". What would this kali be?

thanks

Where I can find the logs of this audit user?

By default, audit logs for SYSDBA or SYSOPER privileges are stored on AUDIT_FILE_DEST directory.

SQL> show parameter AUDIT_FILE_DEST

NAME                     TYPE         VALUE
------------------------ ----------- ------------------------------
audit_file_dest          string       /u01/app/oracle/admin/orcl/adump

For non-sys users, audit logs are stored in data dictionary table called `AUD$' and have several views to get these information.

SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

  "User SYS logged on from kali". What would this kali be?

Kali is the host name/domain name from which the user way logged on to the database server.


What does enabling containment on server incur? Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step":

"Benefits and limitations of contained databases

As with most features, there are benefits and limitations to contained database security. Most of the limitations are specific to containment’s operability with other SQL Server features. If database containment is enabled, you cannot use change data capture, change tracking, and replication"

I wonder:
does setting "Enable contained Databases" to True on server

incur limitations on the whole server, the instance of server or specific database created as contained one?

Apparently, it is only to contained database but, then, why it should be enabled server-wide first?
and what does it incur (for example, if I enable it on server but did not enable it on any particular database)?

Instance-wide settings are typical when the database engine can optimize code paths if the setting is off.  In this instance, having contained database authentication = 1 means the database engine must perform checks on each database that it normally wouldn't need to, in addition to checking login requests against any database that has containment enabled.

The real impact on this is probably quite negligible; if you are forecasting that at some near-term future point you might need to enable containment for a specific database, you may consider enabling the instance-wide setting during some maintenance window.


Can't import flat file into SqlServer I've got this tab-delimited flat file with fifty columns and two million rows.  I am trying to import it into Sql Server 2017 Express.  (I first tried working with it in Excel... hopeless.)

I see two import paths available: SqlServer Management Studio's Import Flat File command, and the Import and Export Data wizard.  Both fail, but in different ways.

With SSMS, it seems to do a very good job of parsing the file and guessing the column types, but it bases the lengths on only the first few hundred rows.  One column is long text, so I've tried setting it to nvarchar(4000) or nvarchar(max) or ntext.  A few others I knew might be longer than the default guessed length of 50 so I bumped up the sizes.  But when I run the import, it says that some column would be truncated or otherwise fail to convert, and it refuses to say which column is the one having trouble.

With the import wizard, on the other hand, it doesn't guess column types at all, and I have to manually set the ones I know.  And thankfully, it has a setting that allows you to tell it per column whether to fail on conversion errors or ignore them.  But it ignores this setting -- it always fails no matter how you set the tolerance, per column or globally.

The good part is that it does identify a column name where it failed, namely the long text column.  But as best I have been able to measure it, there's nothing in it which should have failed.  I don't think any value is over 4000 characters (the longest I've located is 2027) and I'm pretty sure the character set is pure ascii-7.  In fact, it's been forced to all uppercase.

I also tried importing from the abortive Excel sheet I made.  This was thwarted because only the 32 bit importer could see Excel, and only the 64 bit one could see my database instance.

How can I get one or the other of these to allow the import to complete?

I worked with tsv files before and had a similar issue. When you're importing a flat file, I would suggest you change the way SQL Server interprets the column
https://user-images.githubusercontent.com/35179176/34650736-39eaaab0-f3c6-11e7-9706-377c5f0f1170.png
↑Change it to [DT_TEXT] (not unicode) or [DT_WSTR] (for unicode). To be able to change this, you have to import through "Import Data" and not the "Import Flat file" option.


Selecting rows that might contain null values based on column comparison Given a table called table1 whose first few rows look like this:

Idkey DateA     DateB       FieldA    FieldB
1     '2011-06' '2011-03'   20        A
1     '2011-05' '2011-03'   20        A
1     '2011-04' '2011-03'   20        A
1     '2011-03' '2011-03'   20        A
1     '2011-02'  <null>     <null>    <null>
1     '2011-01'  <null>     <null>    <null>
2     '2011-08'  '2011-06'  30        B
2     '2011-07'  '2011-06'  30        B
2     '2011-06'  '2011-06'  30        B
2     '2011-05'  <null>     <null>    <null>

I would like to select all rows for unique Idkeys values starting from the point when DateA = DateB and DateB is in the year 2011. That is, I would like:

Idkey DateA     DateB       FieldA    FieldB
1     '2011-03' '2011-03'   20        A
1     '2011-02'  <null>     <null>    <null>
1     '2011-01'  <null>     <null>    <null>
2     '2011-06'  '2011-06'  30        B
2     '2011-05'  <null>     <null>    <null>

I tried something like:

with subt as (
  select Idkey from table1
  where DateB between '2011-01' AND '2011-12'
  group by Idkey
  order by 1
)

select Idkey, DateA, DateB, FieldA, FieldB,
from table1 b
join subt
on subt.Idkey = b.Idkey
where b.DateA <= nvl(DateB,b.DateA);

But it is not giving me what I want. Any ideas?

You can also try a solution based on window MAX():

WITH
  FirstMatches AS
  (
    SELECT
      *,
      MAX(CASE WHEN DateA = DateB AND DateB BETWEEN '2011-01' AND '2011-12' THEN DateA END)
        OVER (PARTITION BY Idkey) AS FirstDateA
    FROM
      table1
  )
SELECT
  Idkey,
  DateA,
  DateB,
  FieldA,
  FieldB
FROM
  FirstMatches
WHERE
  DateA <= FirstDateA
;

This solution assumes that the order of rows implied by "starting from" is DateA DESC, same as ypercubeᵀᴹ's solution.

Note that while the query will be looking for rows with matching dates specifically among those where DateB is in the year 2011, the output may include rows with DateA dates earlier than 2011. If you only meant to get rows where DateA is in 2011, you can specify that as a WHERE filter in the CTE, also removing the range filter for DateB in the CASE expression as no longer necessary:

WITH
  FirstMatches AS
  (
    SELECT
      *,
      MAX(CASE WHEN DateA = DateB THEN DateA END)
        OVER (PARTITION BY Idkey) AS FirstDateA
    FROM
      table1
    WHERE
      DateA BETWEEN '2011-01' AND '2011-12'
  )
SELECT
  Idkey,
  DateA,
  DateB,
  FieldA,
  FieldB
FROM
  FirstMatches
WHERE
  DateA <= FirstDateA
;


SQL Server Polybase Data Movement Service stopping after few seconds I have been scratching my head for a while now on how to fix this issue, SQL Server Polybase Data Movement Service starts successfully however after a few seconds it just stops. The OS on our server went corrupt last week and force us to reload the OS(Windows Server 2016 Data Center), which meant I had to reinstall and reconfigure Microsoft SQL Server 2017 Enterprise. Since this I cannot get the data movement engine to start and stay running as I have a few process that relies on this I am really in need of some advice.

I have found an Microsoft article below which if I look at error:3409 (Which I cannot seem to find in the logs) is referring to registry access. I have always installed and configured Microsoft Sql Server to only use the service accounts for the Engine and the Agent and leave the other services to the defaults.

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15

See logs

MSSQLSERVER_DET1_Dms_movement

2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:ErrorEvent] (Error, High):
Shared memory segment has not been created or attached.
Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemoryException: Shared memory segment has not been created or attached.
   at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.ThrowIfInvalid()
   at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.SetDistributionCount(Int32 distrCount)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.UpdateSharedMemoryDistributionCount()
   at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.AddDataChannelReceiver(String nodeName, DataChannelReceiver dataChannelReceiver)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.StartDataChannel(Int32 senderQueueSize, Int32 tcpBufferSize, HashSet`1 nodesToConnect)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessStartDataChannel(IMessage message)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessBaseMessage(IBaseMessage message)
2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:InformationEvent] (Info, Normal): Shutting down.
2020/04/03 3:14:28 PM [Thread:12864] [DataChannelReceiver:InformationEvent] (Info, Normal): DataChannelReceiver DataChannelReceiver.Run--DET1:1433: Localhost DET1:1433 connected to RemoteHost DET1:1433: Initiating Run loop.
2020/04/03 3:14:28 PM [Thread:114900] [GeneralInstrumentation:SynchronousLogEvent] (Info, Normal): DMS Exit() called: Forceful shutdown initiated...
2020/04/03 3:14:28 PM [Thread:93816] [DMSDataChannelSenderClient:InformationEvent] (Info, Normal): DMSDataChannelClient created
2020/04/03 3:14:28 PM [Thread:93816] [DataChannelSender:InformationEvent] (Info, Normal): Starting CreditFeedbackReceiver thread: CreditFeedbackReceiver.Run--DET1
2020/04/03 3:14:28 PM [Thread:93816] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: Sender
2020/04/03 3:14:28 PM [Thread:84568] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: CreditFeedback

UPDATE
When stopping the SQL Sever Polybase Engine and then Trying to start the Polybase Data Movement Service, then the service remain started until I start the Polybase Engine Service then back to square 1 again.

It turned out to be a permissions issue with the DWConfiguration/ DWDiagnostics and DWQueue databases that I just reattached after the reinstall of the OS, for some reason the [NT AUTHORITY\NETWORK SERVICE] user did not have the correct permissions on these databases and thus the service kept on failing.
I managed to debug this with an extended events session trying to capture all queries that would touch these databases.
Thank you for all the help and support


Database design tracking daily personal expense I currently have the following in Excel tracking daily expenses:

I would like to turn the following excel sheet into a normalized database. I would also like to capture the descriptions for each expense if its populated. I have 2 approaches in mind:

1) Create a date table and create a separate table for each expense category (as well as a separate corresponding expense description table for each table):

**Date:**
id | date       |
-----------------
1  | 2014-12-01 |
2  | 2014-12-02 |
3  | 2014-12-03 |
4  | 2014-12-04 |

**Rent:**
id |date_fk | amount |
----------------------
1  | 1      | 1000   |

**Restaurant:**
id |date_fk | amount |
----------------------
1  | 2      | 12     |

**Restaurant_desc:**
id | restaurant_fk | description        |
------------------------------------------
1  | 1             | Dinner at Arby's|

2) Create a date table and one general expense table and one expense description table:

**Date:**
id | date       |
-----------------
1  | 2014-12-01 |
2  | 2014-12-02 |
3  | 2014-12-03 |
4  | 2014-12-04 |

**Expense:**
id | date_fk | amount | category     |
--------------------------------------
1  | 1       |  1000  | rent         |
2  | 1       |  120   | parking      |
3  | 2       |  12    | restaurant   |
4  | 2       |  10    | entertainment|

**Expense_description:**
id | expense_fk | description       |
-------------------------------------
1  | 3          | Dinner at Arby's  |

Which approach is better? I'm thinking the 2nd approach is better because its more flexible, if there are additional categories in the future, you won't have to add more tables to accommodate it. Or is there is a better schema for this? please let me know.
Thanks!

I would use a variation of your second approach:

**Expense:**
id | date       | amount | category_fk  | description      |
------------------------------------------------------------
1  | 2014-12-01 |  1000  | 1            |                  |
2  | 2014-12-01 |  120   | 2            |                  |
3  | 2014-12-02 |  12    | 3            | Dinner at Arby's |
4  | 2014-12-02 |  10    | 4            |                  |

**Category:**
id | category_name |
--------------------
1  | rent          |
2  | parking       |
3  | restaurant    |
4  | entertainment |

I don't think you need a separate table for dates. You gain nothing by doing that, unless you want to store extra information for that date, and even then, the date itself would be a natural key for that.

The description of a specific expense has a 1:1 relation with that expense, so it doesn't need a table of its own, unless you want to reuse descriptions (at which point you no longer have a 1:1 relation).

The category needs a separate table, since that will not have a 1:1 relation but a 1:n relation (a single category will be used on several expenses).

If you want to model a n:n relation, where an expense can belong to several categories as well, you will need a cross-reference table like so (removing the category_fk column from the expense table):

**Expense_Categories:**
expense_fk | category_fk |
--------------------------
1          | 1           |
2          | 2           |
3          | 3           |
3          | 4           |
4          | 4           |

As you can see, the Dinner at Arby's is considered both 'Restaurant' and 'Entertainment'.


SSIS installation on the same server as SQL Server We currently have an SSIS installation on the same server as SQL Server. This SQL Server installation is strictly to run SQL Server Agent to schedule the jobs that execute the SSIS packages deployed to this server.  No user DBs, just MSDB and SSISDB, and then a small DB (<100mb) for the DBAs to store certain stored procs and tools to check status, etc.
We have 128GB of memory for the server, and half (64GB) of the memory is dedicated to SQL Server.  The EDW database that we are pushing the data to in SSIS is located on a different server entirely.
Is this set-up correct?  Is it correct to have Integration Services run on the same server that you are scheduling the packages through SQL Server agent on?   I ask because we are running into memory issues, and the DBAs are suggesting that we increase the memory allotted to SQL Server.   Should we give more memory to SQL Server?

I concur with @dan Guzman's comment. You're likely overallocated on memory for SQL Server (and probably for the box itself). Unless you just have continuous package execution and/or suboptimal design patterns in play, that smells like the box will be happy with 1/4 of the resources - just make sure it has the best network speed (assuming data comes from server 1, ssis packages run on server 2 and push data to server 3, you'll likely be network bound). Profile and test, of course.
SSIS is an in-memory run-time engine. When you launch jobs from SQL Agent, they are going to run in their own address space so your 64GB of free memory is going to allocated to the Operating System, dtexec (the process that actually runs packages), etc. Getting into the weeds of an SSIS package, you're only really burning memory when you have data flow tasks and even then, the typical culprits are blocking transformations (aggregations, merge joints, etc) and fat data types n/varchar(max) or varbinary data in the pipeline. Better design can reduce memory contention. And of course, depending on your data source/destinations, you might have to deal with 32 bit drivers so the maximum memory per package execution would be capped under 4GB.
SQL Server is going to need memory to keep the system databases running + the SSISDB for project deployment model. That's likely a trivial amount of resources (disk + memory) but yes, good job on setting a maximum capacity on SQL Server.


Substring a result and renaming it I wrote the below query which works just fine.
The PARAM_ID has a CLC_Q<number> form.

SELECT * FROM DB.TABLE
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45
    ORDER BY SUBSTR(PARAM_ID,6) ASC;

It might be a logical mistake , but I can't do the below,

SELECT SUBSTR(PARAM_ID,6) PAR_ID FROM DB.TABLE
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND PAR_ID BETWEEN 35 AND 45
    ORDER BY PAR_ID ASC;

Although it's a different question, I can't also do this,

SELECT SUBSTR(PARAM_ID,6),* FROM DB.TABLE -- The change is in this line
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45
    ORDER BY SUBSTR(PARAM_ID,6) ASC;

Version

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production

Any further suggestions on how to improve my query would be much appreciated.

Using a CTE will do what you want:

WITH CTE AS
(
    SELECT SUBSTR(PARAM_ID,6) AS PAR_ID, T.* FROM DB.TABLE T
)
SELECT *
FROM CTE
WHERE PARAM_ID LIKE 'CLC_Q%'
AND PAR_ID BETWEEN 35 AND 45
ORDER BY PAR_ID ASC;

or simply using table_alias.*, instead of *:

SELECT SUBSTR(PARAM_ID,6) AS PAR_ID, T.* FROM DB.TABLE T
    WHERE PARAM_ID LIKE 'CLC_Q%'
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45
    ORDER BY PAR_ID ASC;


SQL Server AutoClose option Is AutoClose ON a bad option for a environment with over 15 thousand databases with the same structure?
I know that we should leave this OFF as best practices in 99.99% of the cases. So is it the one edge case that the feature is meant for?
What we should be aware of and also improve in a instance like this?

15K databases on one instance of sql server - depending on the activity, overhead of maintenance, backup time, etc sounds to be an overkill.

You should instead spin up VMs on one powerful host machine and then balance out your databases on multiple VMs.

You are looking at a wrong place for optimization - Auto-Close option and you know that what you are trying to do is not a good practice either !

Remember: Auto-close is deprecated, so one more reason to avoid it.

Seems like you should invest in reading - Multi-Tenant Data Architecture


Monitoring of Oracle Databases Can I monitor all databases on different hosts with single cloud control?
Do I need to install agent on each server?
Do I need any license ?

OEM (Oracle Enterprise Manager or 'cloud control') is licensed under your Enterprise licence agreement. See:

Licensing of Oracle Enterprise Manager Cloud Control by Markus Flechtner
https://community.oracle.com/thread/4197719

So you need to install the OMS (Oracle Management Server) on one server, and an agent on each server that you want to to monitor databases or middleware like Weblogic.  You can use a single OEM installation to monitor your entire enterprise, irrespective of where the hosts are.

Sometimes, due to firewall complications, organizations choose to have more than one OEM installation for ease of use, and sometimes as a backup in case OEM goes down.

However, Oracle licensing is complex and all queries are best handled by your Oracle account manager.


MySQL Full Text index issue I Have a table articles with following data

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  7 | ABCD                  | Following things are not upto date       |
|  8 | RockOn                | is the Following                         |
+----+-----------------------+------------------------------------------+
8 rows in set (0.00 sec)

when i write the query as..

mysql> select * from articles where match(`body`) against('database');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

the result is as expected.

but when i issue the query as

mysql >select * from articles where match(`body`) against('following' in boolean mode);
Empty set (0.00 sec)

why it shows empty set as there is record corresponding to the query.

I have Full text index on body.

mysql> show create table articles\G
*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

It probably seems that following is in the stop words list.
you should check the stop words to see is that the case.


Iterating over the output of a SQL query with PowerShell I have about 300 databases, (most) with a table, [File], and a column [Location] that holds a UNC path to a file. While migrating SANs, some needed files may not have been copied.

This issue materializes when a web GUI attempts to open a file from the table that doesn't exist. I've been trying to put together some PowerShell to iterate over the locations and run Test-Path, since that seems like the most direct way to perform a check.

I'm running into problems in two places:

I also need to check if the file exists in the old location, so I perform the REPLACE on [Location] to get the old SAN name in my query, but can't seem to parse the output correctly.

I would like to Copy-Item directly from what passes the test, but again, can't seem to parse the output correctly.

What am I missing? It's really the last two lines I can't figure out.

Code is below, and hacked together from an example HERE:

$MS='Microsoft.SQLServer'
@('.SMO') |
     foreach-object {
    if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null)
           {"missing SMO component $MS$_"}
     }
set-psdebug -strict
$ErrorActionPreference = "stop" #

$My="$MS.Management.Smo" #
@("Instance1","Instance2", "Instance3") |
   foreach-object {new-object ("$My.Server") $_ } |
    Where-Object {$_.ServerType -ne $null} |
      Foreach-object {$_.Databases -match "DataPattern[0-9]"} |
         Foreach-object {
         $Db=$_
         $_.ExecuteWithResults(‘IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE TABLE_SCHEMA = ''schema'' AND TABLE_NAME = ''File'' AND COLUMN_NAME = ''Location'') SELECT [Location], REPLACE(Location, ''Computer'', ''Old-Computer'') [OldLocation] FROM [schema].[File]’).Tables[0]}|
                ForEach-Object {if(!(Test-Path -Path $_ ) -And (Test-Path -Path $_ -eq     True)) {
                    Copy-Item $_ $_ -ErrorAction Continue } }

Thanks

EDIT:

Ended up getting it to work with the below two lines of code, but it's incredibly slow if I select anything more than the top 50 lines from each table. Any tips on speeding this up?

ForEach-Object {if(!(Test-Path -Path $_.Location ) -And (Test-Path -Path $_.OldLocation -eq True)) {
Copy-Item $_.OldLocation $_.Location -ErrorAction Continue } }

PowerShell offers the ability to do things in many different ways, so this is just the method I would do in solving the problem at hand, based on the information provided.

SMO offers the ability to check the table class to more efficiently find the database in question that contain the table and column specified; instead of trying to use INFORMATION schema views that some folks have a case against ever using them.

I would break this up into sections that will make it more easy to debug. I am assuming you are not worried what UNC path is tied to which server, just making sure the UNC path exist and the file has been moved. So I would first pull all the UNC paths from any database I find the table and column exist.

#Load assembly
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$serverList = 'server1','server2','server3\instance1','server3\instance2'

$searchTableName = 'File'
$searchColumnName = 'Location'

$uncPaths =
foreach ($s in $serverList)
{
    $srv = new-object microsoft.sqlserver.management.smo.server $s

    # The first "where" filters for the database with the specified table name
    # The second "where" filters for the column to exist within the table
    # The "-ExpandProperty Parent" is to go backwards in order to output just
    #   the database name that contains the table and column
    $dbList = $srv.Databases | select -ExpandProperty Tables |
        where {$_.name -match $searchTableName} |
        select -ExpandProperty Columns | where {$_.name -match $searchColumnName} |
        select -ExpandProperty Parent | select -ExpandProperty Parent |
        select -ExpandProperty name -unique

    # If $dbList count is greater than zero means it found a database
    if ($dbList.Count -gt 0)
    {
        #now we need to pull the location paths
        foreach ($d in $dbList)
        {
            $qry = "SELECT [Location] FROM $($searchTableName)"

            # The "-ExpandProperty" can also aid in outputting an object as just a string value
            Invoke-Sqlcmd -ServerInstance $s -Database $d -query $qry | select -ExpandProperty Location
        }
    }
}

# this variable should now contain all the UNC paths you need
$uncPaths

Now in regards to changing the path to the old location I would do something like this when I am iterating through the $uncPaths variable:

foreach ($f in $uncPaths)
{
    $oldPath = $f.Replace("Current","Old")
}


CrystalDiskMark Test Discovers Lopsided Throughput; What Setting Should be Changed? I'm running time trials on a new Windows 2012 Server host with PureStorage FlashArray SSD LUNs attached. I conduct an initial test with CrystalDiskMark and discover write performance is substantially better than read performance.

-----------------------------------------------------------------------
CrystalDiskMark 3.0.1 x64 (C) 2007-2010 hiyohiyo
                           Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]

           Sequential Read :   339.675 MB/s
          Sequential Write :  1454.235 MB/s
         Random Read 512KB :   227.305 MB/s
        Random Write 512KB :  2313.737 MB/s
    Random Read 4KB (QD=1) :    10.719 MB/s [  2616.9 IOPS]
   Random Write 4KB (QD=1) :    74.720 MB/s [ 18242.2 IOPS]
   Random Read 4KB (QD=32) :   367.156 MB/s [ 89637.8 IOPS]
  Random Write 4KB (QD=32) :   175.918 MB/s [ 42948.7 IOPS]

  Test : 4000 MB [G: 87.9% (450.2/512.0 GB)] (x5)
    OS : Windows NT 6.2 Server Standard Edition (full installation) [6.2 Build 9200] (x64)

Is this expected for a FlashArray config or can it be configured at the controller or LUN level to be more optimal for reads than writes? The host will host a data warehouse with tables already in the 250 GB+ range.

This is an old question bumped to the front page, so the MattyZDBA has probably resolved the issue by now, but for future readers:

When posting a question about storage performance it is important to give as much detail about the storage configuration as you can: drive types (traditional, SSD), connectivity to the drives (local nVME drives, Gbit LAN, fibre channel, ...), array size, cache details, ...
While generic artificial benchmarks like CrystalDiskMark have their place, you will get much more useful results from tools designed more specifically for testing database loads on storage arrays. Better yet, if you have a specific application in mind, tests specific to that would give even more realistic results for you (though if it is your own application you might have to construct those tests yourself).
Test : 4000 MB assuming that means the benchmark tool ran each of its steps with 4GB of data, that is not enough to properly test a good modern storage array and could explain the faster-write-than-read behaviour. If the array has a large battery-backed cache then you may find that hardly any of that 4Gb has even gone to the drives yet and all that this has tested is the connectivity between the array and the machine you are testing on. The read operations were presumably reads from parts of the array not just written to or otherwise recently accessed, so they were pulling data from the drives instead of just interacting with faster memory.


What's included in the default OSX 10.8 install of Postgres? I've seen several sites claim that Postgres is installed by default for OSX 10.7 and above and I see there's a psql command in /usr/bin/, but there doesn't seem to be a pg_ctl anywhere.

So is the default install of Postgres something that I can actually develop against or is it just there for OSX itself to use? I just don't want to install an extra version of Postgres and screw something up.

Well, the fact that you have psql (the client) installed, usually means that you have the PostgreSQL server installed as well. However, it does not mean that the server is running.

And, more importantly, OSX is an exception:

  PostgreSQL is the default database on Mac OS X Server as of version 10.7. The standard version of Mac OS X includes only the PostgreSQL commandline client utilities.

(from the documentation)


A login is a member of db_datareader and public roles only, but it is not read-only I'm an accidental DBA in a small company that doesn't have a dedicated DBA.

I need to set up a read-only login/user for SQL Server 2008 database using SQL Server authentication that could be used by power users to run some ad-hoc queries/reports.

I found many posts and articles describing setting up a read-only user, for example:

How to grant users read-only access to all databases

https://www.itsupportguides.com/server-side-tips/sql-management-studio-how-to-create-read-only-users/

http://www.joellipman.com/articles/microsoft/sql-server/create-read-only-database-user-in-sql-server.html

I tried to follow these guides and made sure that the login in question is a member of only db_datareader and public roles, but I still can make changes to the data when I'm logged in as this "read-only" user.

For example, I can run this query in SSMS

UPDATE [dbo].[SoftwareRunLogs]
SET [RunDateTime] = '2010-10-29 13:31:06.133'
WHERE ID = 1

and it updates the row in a table:

(1 row(s) affected)

I expect such query to fail.

When I try to run a CREATE TABLE statement, it fails with the message CREATE TABLE permission denied in database ... I expect UPDATE, INSERT and DELETE statements to fail in a similar way, but it doesn't happen.

Maybe I inadvertently gave write permissions to that user, but I don't know how to check and where to look. I must have missed something trivial.

Any ideas?

There is a server login called ReadOnlyUser and here are its properties:

In the database there is a user called ReadOnlyUser and here are its properties:

I was looking through all items in Security nodes in SSMS and it seems that somehow the properties of the public database role have been changed. They looked like this:

I don't really know how they should look like normally, but clearing the Delete, Insert, Update checkboxes seems to do the trick.

The immediate problem is solved now and I'll have to read more about the public role and what settings it should have.


MS SQL Server - update row value to next row with row id I have a table like below:

Newid1       a   b   c
   1       null  40  50
   2       null  45  55
   3       null  46  56
   4       null  47  57
   5       null  48  58

Now I want to update value in a column by updating C column value into it (For example: 50 to the second row, 55 to the third row, 56 to the fourth row ...). How can I do this with WHILE command.
Thanks all in advance.

It can be done in one set based operation
Using a WHILE risks invoking the "Halloween problem" as you update rows over and over.

It's easier with SQL Server 2012 using LAG

DECLARE @t TABLE
(
    Newid1 int
    , a int  NULL
    , b int
    , c int
);

INSERT INTO @t VALUES(1, NULL, 40, 50)
        ,(2, NULL, 45, 55) ,(3, NULL, 46, 56)
        ,(4, NULL, 47, 57) ,(5, NULL, 48, 58);

-- SQL Server 2012
WITH t AS
(
    SELECT a, LAG(c) OVER (ORDER BY Newid1) AS nextC
    FROM @t
)
UPDATE t SET a = nextC;

SELECT * FROm @t;

But for SQL Server 2005+

DECLARE @t TABLE
(
    Newid1 int
    , a int  NULL
    , b int
    , c int
);

-- SQL Server 2005+
WITH t AS
(
    SELECT Newid1, ROW_NUMBER() OVER (ORDER BY Newid1) +1 AS nextID, a, c
    FROM @t
)
UPDATE t1
SET a = t2.c
FROM
   t AS t1
   JOIN
   t AS t2 ON t1.Newid1 = t2.nextID;

SELECT * FROM @t;


Group not inheriting access privileges for all tables I can't figure out why tables created by one user can be accessed by a read-only user, but not tables created by another user.

I have a viewers read-only group with this access:

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO viewers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewers;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO viewers;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO viewers;

I have two superusers in my database and one read-only user, all members of the group:

 Role name | Attributes | Member of
-----------+------------+-----------
 a         | Superuser  | {viewers}
 b         | Superuser  | {viewers}
 readonly  |            | {viewers}

When I create a table as user a, the readonly user can access it.
But when I create the table as user b, the readonly user gets  permission denied for relation.

When I check the grants, when the table is created by user a, the viewers group gets granted select automatically:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='testa'
;

 grantee | privilege_type
---------+----------------
 viewers | SELECT
 a       | INSERT
 a       | SELECT
 a       | UPDATE
 a       | ...

But if the table is created by b, the the viewers group does not have that select privilege.

I'm sure it's simple, but I am not sure what commands will help me troubleshoot the permissions error. Where can I check to see the existing privileges and get the root of this?

The group to which the users a or b belong does not affect the permissions on the tables created by these users.

There must be default privileges defined for a, but not for b.

Check with \ddp in psql.


Neo4j service won't start with an imported database on Debian I've been playing around with neo4j recently on my Debian 7.8 install. It's awesome!

Today I imported around 100000 nodes and 75000 relationships using neo4j-import tool. I stopped the neo4j service and copied the imported database to /var/lib/neo4j/data/graph.db - but now Neo4j service won't start

Starting Neo4j Server...WARNING: not changing user
process [4963]... waiting for server to be ready....... Failed to start within 120 seconds.
Neo4j Server may have failed to start, please check the logs.

Which is weird because it doesn't wait 120 seconds to start - 5 at most. The only log that is produced is console.log in /var/log/neo4j

2015-07-01 00:10:24.363+0000 INFO  [API] Setting startup timeout to: 120000ms based on 120000
2015-07-01 00:10:26.501+0000 INFO  [API] Successfully shutdown Neo4j Server.

which isn't very useful. When I remove the database, neo4j is able to start. I copied graph.db to my windows installation and it works well, I can search through nodes etc. so perhaps it has something to do with configuration on my Debian? I added a line in  /etc/neo4j/neo4j-server.properties

 org.neo4j.server.startup_timeout=0

but this changed nothing, service start still times out after several seconds. Any ideas?

Here's graph.db messages.log - nothing gets appended when I try to start the service

2015-07-01 01:58:12.142+0000 INFO  [org.neo4j]: Import starting
2015-07-01 01:58:12.282+0000 INFO  [org.neo4j]: Creating new db @ /var/www/ddl/scripts/./graph.db/neostore
2015-07-01 01:58:13.238+0000 INFO  [org.neo4j]: Missing counts store, rebuilding it.
2015-07-01 01:58:13.607+0000 INFO  [org.neo4j]: No lucene scan store index found, this might just be first use. Preparing to rebuild.
2015-07-01 01:58:13.663+0000 INFO  [org.neo4j]: No lucene scan store index found, this might just be first use. Preparing to rebuild.
2015-07-01 01:58:13.952+0000 INFO  [org.neo4j]: Rebuilding lucene scan store, this may take a while
2015-07-01 01:58:13.955+0000 INFO  [org.neo4j]: Lucene scan store rebuilt (roughly -1 nodes)
2015-07-01 01:58:30.337+0000 INFO  [org.neo4j]: IMPORT DONE in 18s 182ms. Imported:
  95941 nodes
  158227 relationships
  369482 properties
2015-07-01 01:58:30.341+0000 INFO  [org.neo4j]: About to rotate counts store at transaction 1 to [/var/www/ddl/scripts/./graph.db/neostore.counts.db.b], from [/var/www/ddl/scripts/./graph.db/neostore.counts.db.a].
2015-07-01 01:58:30.344+0000 INFO  [org.neo4j]: Successfully rotated counts store at transaction 1 to [/var/www/ddl/scripts/./graph.db/neostore.counts.db.b], from [/var/www/ddl/scripts/./graph.db/neostore.counts.db.a].

It turns out that what I needed to do is make sure my permissions are right.
I fixed it with

chown -r Neo4j:adm graph.db

and everything seems to work fine now. I think Neo4j should produce some sort of warning if it can't access graph.db


Is it possible to set a timer for autovacuum in PostgreSQL I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My queries don't run during at this time and I can only resume after an hour of waiting. After researching why this could be, I am assuming that my database is starting autovacuum processes, which locks up my datatables.

Is it possible to schedule a time for autovacuum or to set a time where autovacuum should not run? I would like to avoid turning the autovacuum off.

You can set a time where autovacuum should not run.

Here's how to (assuming You're on linux):

Create a script to turn off/on autovacuum
The script should contain :

sed -i 's/autovacuum = on/autovacuum = off/g' postgresql.conf

pg_ctl -D PGDATA reload

Place the script on cron.

Let's say You have cron to turn off autovacuum at 2 PM and then have cron to turn on autovacuum at 3 PM.

Hope it helps.


dynamically get last x months and output as a format Using today's

date, I need to be able to go back x months and output in the format of YYYY_MM

At the moment, I am using:

select
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -3, GETDATE())) as varchar(2)), 2)

Which will give me a output of:

  2015_08

The issue I am having is that if I were to go back 12 months using:

select
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -12, GETDATE())) as varchar(2)), 2)

The output would still be:

  2015_11 (notice the year is still the same)

I am using SQL 2008

EDIT: Solution

DECLARE @table12 AS varchar(50)
SET @table12 = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -12, GETDATE()),102), '.', '_')

I hope that you are not repeating that expression for dateadd(month, -4, dateadd(month, -5, etc. Here is a much tidier way to develop an aggregate query that covers the past 12 months:

DECLARE @start DATE = DATEADD(DAY,1-DAY(GETDATE()),GETDATE())
;WITH Last12Months AS
(
  SELECT TOP (12) m = DATEADD(MONTH, -(ROW_NUMBER() OVER
    (ORDER BY [object_id])-1), @start)
  FROM sys.all_objects
  ORDER BY [object_id]
)
SELECT m.m, COUNT(o.[key])
FROM Last12Months AS m
LEFT OUTER JOIN dbo.OtherTable AS o
  ON o.DateTimeColumn >= m.m
  AND o.DateTimeColumn < DATEADD(MONTH, 1, m.m)
GROUP BY m.m
ORDER BY m.m;

Now, this will output the date as:

2014-12-01

If you really need 2014_12, then either (a) format it in the presentation tier (best practice!) or (b) do something really inefficient and format it in T-SQL. So the SELECT part of the query above becomes:

SELECT m = REPLACE(CONVERT(CHAR(7), m, 120), '-', '_'), c
FROM
(
  SELECT m.m, c = COUNT(o.[key])
  FROM Last12Months AS m
  LEFT OUTER JOIN dbo.OtherTable AS o
    ON o.DateTimeColumn >= m.m
    AND o.DateTimeColumn < DATEADD(MONTH, 1, m.m)
  GROUP BY m.m
) AS wasted_cpu_cycles
ORDER BY m;


mysql how to copy foreign key data from one table to another I have 3 tables.
Each table has its own primary key "id"
table 2 has a foreign key that references table 1's primary key
table 3 has a foreign key that references table 2's primary key.
I want to add to t.3 the foreign key that links t.2 - t.1 so that t.3 is also linked to t.1. I cant figure out how to copy the relevant column from t.2 to t.3.

That depends on youir table definition .
If your id fileds are auto increment

CREATE TABLE table1(id int AUTO_INCREMENT PRIMARY KEY)

CREATE TABLE table2(id int AUTO_INCREMENT PRIMARY KEY,t1id int,
    FOREIGN KEY (t1id) REFERENCES table1(id))

CREATE TABLE table3(id int AUTO_INCREMENT PRIMARY KEY,t2id int,
    FOREIGN KEY (t2id) REFERENCES table2(id))

INSERT INTO table1 VALUES(NULL);

INSERT INTO table2 VALUEs(NULL,LAST_INSERT_ID());

INSERT INTO table3 VALUEs(NULL,LAST_INSERT_ID());

SELECT t1.*,t2.*,t3.*
FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.t1id
INNER JOIN table3 t3 ON t2.id = t3.t2id

id | id | t1id | id | t2id
-: | -: | ---: | -: | ---:
 1 |  1 |    1 |  1 |    1

db<>fiddle here
In other cases you have to get the last id number from the father table, which is dependent on which type if id you have.


Denormalized table designs in normalized database? There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, Datetime and float. When asked for a reason for such denormalized table, we were told thats how the record record sets were provided by vendors and hence the mapping table has to be designed in that approach. In addition, table will be used for cross joining across other normalized tables and table will potentially grow to larger size. Currently there are few tables (with 375 columns) with similar design and average of 3 million rows or more on the same database. These existing denormalized tables are partitioned by date. No performance issues on these existing tables as they are not being heavily used yet.

Questions:

Other than saying it is a bad design, do you see any obvious issues with such design that can convince the developers to reconsider their designs strategy in a normalized environment. Of course Normalized versus Denormalized have their own pros and cons.
In your experience, have you seen any common real time performance issues with such columnar tables such as locking, takes longer for DML etc? The developers even mentioned about considering MongoDB as their alternative if SQL Server shows any performance issues.
Do you have some high level performance issues that could possible occur if such tables are designed more often in OLTP. Please note that the company is flexible with adding CPU or memory if needed for any IO or CPU issues.

I know in SQL 2012,there is columnstore index but haven't explored yet.

Some issues to bring up:

For OLTP, inserts will be really slow in a table that wide
You will be wasting a lot of space by repeating redundant information
Columnstore is a non-modifiable index type so you can't use it in an OLTP environment
You greatly complicate referential integrity controls this way.  You can't just make foreign keys to make sure you are getting valid values for fields.
Indexing will be a nightmare

The real issue here is the developers not understanding design.

Keeping the client data in it's native format is FINE.  I do  this kind of thing for a living, and I get tables with 500+ fields all the time.  The way to handle it is to separate your RAW data from your BUILT data.

If the client gives you a massively wide table, you need to normalize it yourself to make a usable data set.  There's nothing stopping you from creating a process that breaks out that data into appropriate tables.