Is it worth to optimize queries with EarlyTermination Timeout? Right after running Ola's IndexOptimize to rebuild all indexes and statistics, I cleared the data and plan cache and ran a very complex ugly query to get a baseline of it's performance. The query actual plan shows ReasonForEarlyTermination=Timeout and skews for Est Rows and Actual Rows (actual rows factor 10,000 compared to est.).

I wonder if this imbalance of the number of rows maybe a result of the Timeout - I thought it should be appropriate after the index & statistics rebuilding?

Does optimizing queries that end with ReasonForEarlyTermination=Timeout make any sense at all, if it's impossible to change the query itself? Since I planned to do some index tuning and re-running the query, isn't it impossible to be sure about the results of the possible improvements, because the next query plan creation will certainly timeout again and so might be different, with different results?

Tec. Details:
Sql Server 2008 R2 Dev. Ed. 10.50.6000.34 DB Comp. Lvl 80.

I wonder if this imbalance of the number of rows maybe a result of the Timeout

Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate".

See this related question for additional information about the Timeout reason:

Queries without Good Enough Plan Found

  I thought it should be appropriate after the index & statistics rebuilding?

You have "a very complex ugly query". It is unreasonable to expect the cardinality estimator to work well in such cases. Try computing estimates yourself from the single-column (possibly sampled) statistics histograms and single-value density information to get a feeling for this.

As query complexity builds, so the quality of estimates tends to go down. This is inevitable, if you consider that the input to one estimation is usually the output of another. Thus, errors tend to accumulate. This is made much worse by the use of "ugly" (read: non-relational or otherwise hard-to-estimate) conditions.

  Does optimizing queries that end with ReasonForEarlyTermination=Timeout make any sense at all, if it's impossible to change the query itself?

Sure (but no differently than for any other problematic plan). In general, one can break the query up, express it using different syntax, or any one of a hundred different "tricks" may be used.

Even where the query itself cannot be changed, if a good plan can be found, it can be forced with a plan guide. More generally, adding new statistics and/or indexes can provide better information to the optimizer and cardinality estimator. This can also affect the code path taken through the optimizer in complex ways. I will not attempt to list all the things that can be done outside of changing the query text.

  ...the next query plan creation will certainly timeout again and so might be different, with different results?

The point is that by providing better information or data access methods, you will generally improve your chances of getting a good plan. The optimizer is not random, so for a given state of the database, the same plan will be selected, regardless of timeout, or other termination reason.

The real key to success is to avoid "very complex ugly" queries, of course.


mysql: Show GRANTs for all users MySQL's SHOW GRANTS shows the permissions of the current user.

Is there a way to log in as root and show the permissions of all users?

select * from information_schema.user_privileges;

EDIT:

As mentioned by Shlomi Noach:

  It does not list database-specific, table-specific, column-specific,
  routine-specific privileges. Therefore, the grant GRANT SELECT ON
  mydb.* TO myuser@localhost does not show in
  information_schema.user_privileges. The common_schema solution
  presented above aggregates the data from user_privileges and other tables
  to give you the full picture.


Connect to LocalDB via SQLCMD in PowerShell I'm trying to access the "localdb\MSSQLLocalDB" server on my computer through PowerShell with the SQLCMD utility. I'm using PowerShell v5, .NET v5.0, and the server name is (localdb)\MSSQLLocalDB when I connect to it in Microsoft SQL Server Management Studio 2014.

PS C:\> sqlcmd -S localdb\MSSQLLocalDB and PS C:\> sqlcmd -S .\localdb\MSSQLLocalDB result in this error:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF].

I queried the server name in Management Studio with SELECT @@ServerName and used that after the -S in the above command and got the same error.

PS C:\> sqlcmd -S localdb gives this error:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes
  Provider: Could not open a connection to SQL Server [53]

Other notes: I'm able to connect to the server and work with a database named testdb01 in a C# console app using System.Data.SqlClient with this connection string:

"Data Source=(localdb)\\mssqllocaldb;Initial Catalog=testdb01;Integrated Security=SSPI;"

The casing of the instance name does not matter.

You need to try:

sqlcmd -S "(localdb)\MSSQLLocalDB" -d testdb01

The "localdb" part needs to be enclosed in parenthesis as that is a special syntax that points to a SQL Server Express LocalDB-specific API that allows for automatic instance start-up upon being referenced in a connection string. The whole instance name needs to be enclosed in double quotes. The command-line shown above works for me, at least outside of PowerShell.


Store data of feedback form I am a novice at database management and doing a small project on Feedback System.
My question is that I want to know that how i should store the data of feedback form. Suppose i have different feedback form for different subjects and each student submits the form so how i should store and manage the data?
 My feedback form may have around 20 questions which can be descriptive type also. Do i have to create table for each student, in that case my tables will be too large or i have to create table for each subject?

You should create a table that holds the form and it has an form ID and the form name. Then another table that holds the questions for that particular form. The fields would be the formid from your first table and then the quesiton ID and the question. The question id would be continuous unique range of numbers (so if you have 3 forms and each has 10 questions, questionid needs to be 1 through 30). And finally a third table that has the questionid and the answer. You can also have other fields on these tables such as who took it, who created it, when it was created, when it was taken, etc.


Which index types do not support online index rebuilds? In SQL Server 2012 Enterprise Ed., which index types do not support online index rebuilds?
and why?
and which activity-operations can prevent online index rebuilds?

This is in the documentation:

  Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and text.

  Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

  Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

Other limitations and considerations are listed as well.


Postgres Grouping of Like Results Our database is running on Postgres 9.3. We have a table called notifications.

Notifications contains id, type, triggering id, source id, and some other information.

What I am trying to do is select the notifications and then group them on their source ID.

So if I have the following:

id     type     source_id     triggering_id
1     comment     1234        4567
2     comment     1234        0123
3     comment     1234        5432

I would like to get the following back:

source_id, triggering_id(s)

I don't know if this is even possible. I'm pretty new to the DBA stuff.

If I understand you correctly you want a comma separated list of triggering_id's

select source_id,
       string_agg(triggering_id::text,',') as id_list
from notifications
group by source_id;


How to verify if a SQL Account is still used? We have a SQL Server Login on a SQL Server 2008, and no one seems to know if the account is actually being used or not. It is assumed it is being used, as it is configured in a Linked_Server connection, however no one knows if it is still really being used, and if so for what. How do we verify, aside from disabling and waiting for a squeal?

An additional, easy, option is to log all successful & failed logins.  Under the server properties tab

This will write any use (or attempted use) of the login to the SQL Server log.  From there you can programatically search the log.

USE Test
GO

-- P1 is the file number starting at 0
DECLARE
@p2 INT = 1,
-- P2 1 for SQL logs, 2 for SQL Agent logs
@p3 NVARCHAR(255) = 'UserName',
-- P3 is a value to search on
@p4 NVARCHAR(255) = ''
-- P4 is another search value

IF OBJECT_ID('IOErrorLog') IS NULL
    CREATE TABLE IOErrorLog (
            ServerName nvarchar(256),
            LogFileName nvarchar(4000),
            Occurances int,
            LogDate datetime,
            ProcessInfo nvarchar(50),
            ErrorText nvarchar(max),
        CONSTRAINT pk_IOErrorLog PRIMARY KEY (ServerName, LogDate, LogFileName)
    )

----------------------------------------------------------------------
-- This part of the code was found here:
-- https://ask.sqlservercentral.com/questions/99484/number-of-error-log-files.html

DECLARE @FileList AS TABLE (
    subdirectory NVARCHAR(4000) NOT NULL
    ,DEPTH BIGINT NOT NULL
    ,[FILE] BIGINT NOT NULL
);

DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000);
SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000));
SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'\', REVERSE(@ErrorLog))) + N'\';

INSERT INTO @FileList
EXEC xp_dirtree @ErrorLogPath, 0, 1;

DECLARE @NumberOfLogfiles INT;
SET @NumberOfLogfiles = (SELECT COUNT(*) FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%');
-- SELECT @NumberOfLogfiles;
----------------------------------------------------------------------

CREATE TABLE #temp (
    LogDate datetime,
    ProcessInfo nvarchar(500),
    ErrorText nvarchar(max))

DECLARE @p1 INT = 0

WHILE @p1 < @NumberOfLogfiles
BEGIN

BEGIN TRY
    INSERT INTO #temp
    EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
END TRY
BEGIN CATCH
    PRINT 'Error occurred processing file ' + cast(@p1 as varchar(10))
END CATCH

SET @p1 = @p1 + 1
END

INSERT INTO IOErrorLog
    SELECT DISTINCT @@SERVERNAME,
        SUBSTRING(ErrorText,charindex('[',ErrorText)+1,
            charindex(']',ErrorText)-charindex('[',ErrorText)-1),
        SUBSTRING(ErrorText, 27, patindex('%occurrence%',ErrorText)-28),
        LogDate, ProcessInfo, ErrorText
    FROM #temp
    WHERE #temp.LogDate NOT IN (
        SELECT LogDate
        FROM IOErrorLog
        WHERE ServerName = @@SERVERNAME)

DROP TABLE #temp

SELECT * FROM IOErrorLog

The down side here is that you are logging the login successes & failures for everyone.  It could bloat your error log a bit.  It's generally considered a best practice to pull at least the failed logins though.


Query for similar syllables on a field I have a table called toponim. This table contain field called namspe. I would like to find words in this field that contain similar syllables such as Toli-toli, Bagan Siapi-api, Fakfak, Oro-oro Ombo.

At first I use this query, but it won't work if the word that contains similar syllables is in the middle or in the end of the sentence. Any ideas?

select namlok, namspe
from toponim
where left(namspe,4) = substring (namspe,5,4);

What version of Postgres are you using?

If I understand correctly, you would like to match records with similar words. There are several ways to approach this. I'd suggest taking a look at a few features and seeing if one sounds like a good match for you.

The pg_trgrm extension included with standard distributions is worth considering:
https://www.postgresql.org/docs/current/pgtrgm.html

This tool breaks words into a chunks of three letters, and then (with the help of an index) quickly compares two strings to see how similar they are, based on the three letter chunks.

Trigrams are a good general-purpose approach, but you sound like you are matching on real words, not just random strings. What you describe sounds like the process used to prepare words for a "lexical" Full Text Search in Postgres:

https://www.postgresql.org/docs/current/textsearch.html

The idea with a "lexical" analysis is that strings are converted into a common set of base words. For example, in English, singular, plural, and different tenses are converted into a common word to simplify certain kinds of matches. Sometimes the "word" isn't even a real word, it's just a common root-like word used for this purpose.

In your case, I believe that 'Botol-botol' and 'Botol' would both be converted to 'botol'. Likewise, "merdeka-merdeka" and "merdeka" could be translated to "merdeka" for comparison. It's even possible to have a thesaurus, so perhaps "mandiri" could also be translated to "merdeka". For all of this to work automaticall requires an Indonesian dictionary for Postgres. I just checked my installation, and do not find Malay or Indonesian:

SELECT dictname FROM pg_catalog.pg_ts_dict;

Perhaps someone knows how to add Indonesian to Postgres?

If there is no suitable dictionary for your language available, you may be able to make progress with one of the installed dictionaries. There you will get a list of words parsed. There's only one text parser in Postgres, and I do not believe that it will break words on the - character, which would be useful in your case. To work around this, I've used the replace function to change hyphens into spaces so that the Postgres parser will see each part as a distinct word. Perhaps this is a terrible idea, but it should help you see how the system can work.

/* Toli-toli, Bagan Siapi-api, Fakfak, Oro-oro Ombo */
select to_tsvector('simple', replace('Toli','-',' ')) union all
select to_tsvector('simple', replace('Toli-toli','-',' ')) union all
select to_tsvector('simple', replace('Bagan Siapi-api','-',' ')) union all
select to_tsvector('simple', replace('Fakfak','-',' ')) union all
select to_tsvector('simple', replace('Oro-oro Ombo','-',' '))

Below are the results:

to_tsvector
'toli':1
'toli':1,2
'api':3 'bagan':1 'siapi':2
'fakfak':1
'ombo':3 'oro':1,2

Notice that "Toli" and "Toli-toli" both become "toli", which is what I think you are trying to accomplish.

Best would be a language adapted to your language, of course.


How do I recover data from the postgres data directory on my hard drive? I had an old iMac which stopped working a while ago
I take weekly backups of the database I was updating which contains feed items from several rss feeds
I am still missing the data for the last week
I currently have the hard drive of the old machine which contains the postgres data directory (it was an Intel based iMac)
My new machine is an Apple M1 based mac mini and I want to load all those feed items
I want to recover a particular table called feed_items from a specific database called ch_v3 from the data directory on the hard drive containing old postgres files
How do I recover the 100k+ feed items which are stored in raw format on my old hard drive into my new postgres installation on the new machine?

If you need a method to restore the database but do not have an Intel/AMD-based system available, then a virtual machine on Amazon, DigitalOcean, or elsewhere may give you what you need. If you’ve never had an Amazon Web Services account, you can even do all of this for “free”.
The process might work like this:

Sign up for an account with a VPS provider
Configure it with an operating system of your choice. For the sake of this example, I’ll use Ubuntu Server 20.04 and PostgreSQL 12
Install PostgreSQL
sudo apt install postgresql postgresql-contrib

Confirm the installation was successful by connecting to the database:
sudo -i -u postgres
psql

So long as you can connect, then it’s all good. Let’s exit:
\q

Now we need to stop the server:
service postgresql stop

With the basics in place, it’s now time to do some file management.

Delete the files located in the PostgreSQL data directory
sudo rm -Rf /var/lib/postgresql/12/main

Copy the old data files to that location
Set file permissions accordingly:
sudo chown -R postgres:postgres /var/lib/postgresql/12/main
sudo chmod -R u+rwx,g-rwx,o-rwx /var/lib/postgresql/12/main

Restart PostgreSQL
sudo service postgresql start

Get your data back

All in all, this may be about 30~45 minutes of work. Hope it allows you to recover the missing records.

Note: If you choose to create an AWS account and use their free tier options, remember to terminate the instance when you’re done to avoid any unexpected charges one year from now.

Note 2: Because these are virtual machines, if anything goes wrong, simply terminate the machine and start over.


In which server does the snapshot agent run? While configuring a publisher for replication, we execute sp_startpublication_snapshot SP and provide the publication name as a parameter to the SP. We do not specify the distribution server's login/password. So, I am confused if the snapshot agent runs on the publisher or distributor. Even if it runs on distributor then how can we trigger it from publisher (sp_startpublication_snapshot has to run at publisher as per MSDN tutorials)

The Snapshot Agent (snapshot.exe) runs on the Distributor.

From Books Online:

Replication Agents Overview

  Snapshot Agent

  The Snapshot Agent is typically used with all types of replication. It
  prepares schema and initial data files of published tables and other
  objects, stores the snapshot files, and records information about
  synchronization in the distribution database. The Snapshot Agent
  runs at the Distributor. For more information, see Replication
  Snapshot Agent.

However, the stored procedure sp_startpublication_snapshot is executed at the Publisher on the publication database and is used to start the Snapshot Agent job that generates the initial snapshot for a publication.

When you configured replication and created the publication, you specified the accounts in which the Snapshot Agent will run under at the Distributor and the Publisher.  This account information is saved.  This is covered in Snapshot Agent Security.

Even though you execute sp_startpublication_snapshot from the Publisher, it already has all of the necessary information to connect to the Distributor and start the Snapshot Agent.


SQL Server 2012 - How to view the locks' log? I'm using SQL Server 2012 Express Edition, may I know how can I view all the lock/block processes which were recorded in my database? The reason I want to view the previous locked/blocked processes is SQL Server only allows us to view the current locked process in the database.

I found that I have few timeout errors in my SQLException log file, so I would like to know is there a way to view or query the past records on the locks/blocks that caused the time out.

And I did not turn on my blocked process report for my database T.T

If you haven't already turned on the blocked process report, then it's not possible to jump back in time to view past blocked processes.

However, I find that where there's blocking, these two already-on things help:

First, SQL Server tracks which indexes are involved in blocking. It doesn't tell you which queries are involved, nor is it permanently reliable (the data disappears at odd times), but it's a start. You can query sys.dm_db_index_operational_stats to learn more, or use the open source sp_BlitzIndex to see it. sp_BlitzIndex identifies the indexes involved in blocking as "Aggressive Indexes" - that can be a little confusing, so here's a post on how to interpret those warnings.

Second, if there's blocking, there's sometimes also deadlocking. And good news! SQL Server tracks deadlocks by default in the system health session, and you can indeed query those with the open source sp_BlitzLock. It doesn't hurt to give that a shot - it may identify which queries are causing you problems.

(Disclaimer: I wrote some code in those open source procs, and I wrote the blog post referenced in here.)


Duplicate login details per user in error log Being able to view details of who successfully logs into a database and which user's login failed via the error logs is a good thing. However I have a scenario that I need help with:

For some reason the success and failure details are logged multiple times and as such if I login to a SQL Server 2008 R2 instance this very minute, I see multiple "Login successful for user ..." messages.

Can anyone help to resolve this (without turning off the option to save login success/failure) as it is filling up the logs and pushing down relevant information.

I realize this doesn't answer the question "why does the log file have multiple entries for 'Login succeeded...'", however since you also seem to be concerned about the other log entries being "drowned-out" by these low-importance login messages, I thought I'd add the following as an answer.

Instead of using SQL Server Management Studio's built-in interface for looking at log files, you can use the following code that will allow you to ignore log messages you are not interested in.  This allows you to concentrate on messages that may be more immediately relevant.

IF NOT EXISTS(
    SELECT s.name + '.' + t.name
    FROM tempdb.sys.tables t
        INNER JOIN tempdb.sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name + '.' + t.name = 'dbo.ErrorLogAnalysis'
    )
BEGIN
    CREATE TABLE tempdb.dbo.ErrorLogAnalysis
    (
        LogDate DATETIME,
        ProcessInfo NVARCHAR(255),
        [Text] NVARCHAR(MAX)
    );
END

TRUNCATE TABLE tempdb.dbo.ErrorLogAnalysis;
EXEC sp_ReadErrorLog 0,1;
INSERT INTO tempdb.dbo.ErrorLogAnalysis
SELECT ela.*
FROM tempdb.dbo.ErrorLogAnalysis ela
WHERE ela.Text NOT LIKE 'Login succeeded%'
ORDER BY LogDate DESC;

It may be helpful to point out that client-side code is quite likely to use more than a single connection to SQL Server.  This may in fact be the precise reason why more than a single entry is recorded in the SQL Server Error Log.

To prove this, I've written a very simple VB.Net command-line app that creates 5 connections to my local SQL Server instance.  Once all five connections have been opened, the app executes a simple query against the server showing 5 session_id values that the app has open.  It then closes all 5 connections, and exits.

This is the code:

Module Module1

    Sub Main()

        Dim sConnString As String =
            "SERVER=localhost;" & _
            "DATABASE=master;" & _
            "TRUSTED_CONNECTION=True;" & _
            "Application Name=MultiConnTest"
        Dim sqlC1 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC2 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC3 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC4 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC5 As New SqlClient.SqlConnection(sConnString)
        sqlC1.Open()
        sqlC2.Open()
        sqlC3.Open()
        sqlC4.Open()
        sqlC5.Open()
        Dim sqlCMD As New SqlClient.SqlCommand( _
            "SELECT session_id, login_name " & _
            "FROM sys.dm_exec_sessions des " & _
            "WHERE des.host_name = HOST_NAME " & _
            "   AND des.program_name = 'MultiConnTest';", sqlC1)
        Console.WriteLine("Testing multiple connections to SQL Server.")
        Console.WriteLine()
        Console.WriteLine("Sessions currently open on the server, with username")
        Dim sqlReader As SqlClient.SqlDataReader = sqlCMD.ExecuteReader
        Dim iSessionID As Int32
        Dim sUserName As String
        While sqlReader.Read
            iSessionID = sqlReader.GetValue(0)
            sUserName = sqlReader.GetValue(1)
            Console.WriteLine(iSessionID & vbTab & sUserName)
        End While
        sqlReader.Close()
        sqlCMD = Nothing
        sqlC1.Close()
        sqlC2.Close()
        sqlC3.Close()
        sqlC4.Close()
        sqlC5.Close()
        Console.WriteLine("Press any key to exit")
        Console.ReadKey()

    End Sub

End Module

The console output from running this against my machine is:

Testing multiple connections to SQL Server.

Sessions currently open on the server, with username
52      Max
53      Max
54      Max
55      Max
56      Max
Press any key to exit

The SQL Server Error Log, after enabling auditing of both successful and unsuccessful logins, shows:

LogDate                 ProcessInfo Text
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]

I think you can see from this a possible source of your error log entries is not some errant SQL Server behavior that can be "disabled", but is far more likely to be the desired result arising from the combination of auditing successful logins, and client applications that use multiple connections.

I would check the source code of whatever applications are running against your SQL Server to determine if they are in-fact using multiple connections.

If they are, I would use sp_readerrorlog to look at the error log.  I might even go so far as to create a stored procedure to encapsulate the functionality of sp_readerrorlog to produce output in whatever format you desire.


Oracle 12c Release 2 Database Configuration Assistant installation on CentOS - The port is already in use I know this error has already been intensively discussed in this thread here: Resolving port 5500 not free issue during Oracle 12c Release 2 Database Configuration Assistant installation on CentOS

However, despite the solutions proposed I can't install Oracle 12c on CentOS 7.

I skiped the Database Configuration Assistant during standard installation process in order to run it again using Xming. There it is possible to enter the EM database express port manually.

I tried almost any port number, I always get the error message shown in the screenshot.

Do you have any suggestions what else I could do?

EDIT:

Content of /etc/hostname:

CentOS-Dev-db-12c.Company.local

Content of /etc/hosts:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1       localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.10.113 CentOS-Dev-db-12c.Company.local

Oracle support doc [DBT-06103] The port (5,500) is already in use. ACTION: Specify a free port (Doc ID 2277154.1) says to put the unqualified hostname into /etc/hosts too. i.e:

192.168.10.113 CentOS-Dev-db-12c.Company.local CentOS-Dev-db-12c


Extended Events, CPU_TIME I want to create a new session in Extended Events to track Queries that use lots of CPU_Time, but whats the Unit of CPU_TIME? ms? Microseconds?...

Duration seems to be Microseconds but I don't really know CPU_TIME and can't find any information regarding this...

Thanks,
Jimm

The sys.dm_xe_object_columns DMV shows metadata for each of the columns available for each event. Included is a "description" column, which is useful in answering your question.

select
    name
    , object_name
    , type_name
    , description
from
    sys.dm_xe_object_columns
where
    name = 'cpu_time' -- any XE column name here

This will show you all of the extended event objects that have a cpu_time column, and the description will tell you what unit of measure the column uses.

You can see here that units may vary depending on the object. For example:

sql_statement_completed: Indicates the CPU time (in microseconds) that is consumed by the statement.
sql_batch_completed: The CPU time (in milliseconds) used by the batch.


Replication slows down on massive deletes First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue.

We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so server A is the source of data to servers B, C and D. There is one-way peer-to-peer replication between servers. Network is 1Gbps.

  __ A __
 /   |   \
 B   C    D

We have some denormalized table in our database with about ~700k rows. Table is about of 1Gb in size (and 5.5Gb of indexes) This table constantly updates - so all content in this table is renewed in ~2 hours (i.e. old rows are deleted and new are inserted). This is done in transactions of 5k records.
All works fine on server A, but replication of data from A to B, C and D slows down and delay could be about one hour between these servers.

Our DBAs say that it happens because of big delete transactions. They say that SQL Server executes delete operations on subscriber sequentially on each row, one by one. Is there a way to change this behavior if it does it so?

Actually, I'm not a big fan of replication, I would prefer to use some kind of messaging and publish-subscribe pattern to synchronize data between servers, but replication is the main way to transfer data in a company that I work for now. So it will be kinda hard to change these things.

Any thoughts how to improve performance of replication?

First there's no such thing as one way peer to peer (that I know of).  Are you sure you aren't using transactional replication?

Are the deletes done via stored procedures?  If so you can replicate the stored procedures as well as the tables (all in the same publication, this will require a reinit of the subscribers).  This way the stored procedure call will be replicated instead of the actual row by row delete.

With only 700k rows in the table, even if you blew them all away and reloaded them the replication shouldn't get that far behind unless you have a really slow link between the servers (which you didn't specify).


Batch - Update vs Insert then Delete There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 are Oracle 12c. Now, the issue is there are  about a million and a half records in database2 and based on statistics, everytime the ETL will be run, about 45% of the records in database2 is likely to be updated.

Now we're thinking how long could it take to update about 675,000 records every time the ETL is run. We would want it to be execute at a certain time window and not go on running beyond that. Would it be more efficient to insert then delete instead? rather than update them all? We would also like the implementation to be future proof - although there is db purging for database2 (delete those more than 3 years), there is a possibility for data to grow at a high rate in the next few months.

If the data is purely read-only once it arrives in the target database (and you have lots of disk space available), here's an alternative you might consider.

-- Two tables with the SAME structure
create table table1_a ( ...
create table table1_b ( ...
-- No Application permissions on these tables

create view table1_read  as select * from table1_a ;
create view table1_write as select * from table1_b ;

grant select                         on table1_read to application_role ;
grant delete, insert, select, update on table1_write to etl_role ;

The application that consumes the loaded Data always reads from table1_read.

Your application doing the ETL always writes to table1_write.

Apart from the background work in the database churning all that data across, there is no impact on the Application ...  until, at the end of a successful load, you invoke a stored Procedure that works out which View is looking at which Table and then switches the definitions of the two Views.

create or replace view table1_read  as select * from table1_b ;
create or replace view table1_write as select * from table1_a ;

This allows you to use really fast, database-level utilities to trash and reload one of the tables while the Application continues to read the other and then with minimal, if any, impact on the Application, makes the new data available and frees up the other table ready for loading next time.

OK, you are doubling your data storage requirement, having two copies of the data lying around but if, for any reason you suspect the newly loaded Data is wrong, you can [very] quickly revert to "yesterday's data" by switching the Views over again.  No re-running the ETL load process to get back to a stable, working system.

Sure; it's a sledgehammer approach, but is has the advantage of simplicity and has proven remarkably resilient.  That Procedure is the only "fiddly" bit but it can be coded in such a way that it only has to be written and tested once and reused for any number of tables treated in this way.


Limit memory used for mongoDb Is there any way to limit using RAM for mongodb on Debian? I'm looking for a solution fo about 8 hours, but have no results.

I have heard that some folks have had success using lxc to isolate MongoDB on Linux. There are other container technologies out there, but that seems to be the most mainstream option.


PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it? I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data.

I am beginner in PostgreSQL.

As I see in documentation and on this question also https://stackoverflow.com/questions/35289888/restore-postgres-database-dump
for succesfull restoration of the database we need to:

* DROP database
* CREATE database
* execute psql dbname < dumpfile

I am really confused (based on the behavior from all other databases ) that we need to drop and recreate database if we want to restore? It's hard for me to believe that Postgres really is implemented in that way.

If you run pg_dump with the --clean option then the generated SQL dump would contain drop statements for all dumped objects (note that when using the custom format and pg_restore you don't need to decide on this when dumping, but when restoring).

However if the target database contains tables (or other objects) that were not contained in the source, those would not be dropped.

Using pg_dump --clean (or pg_restore --clean) is pretty much the same behaviour as with Oracle's  impdp using the TABLE_EXISTS_ACTION=REPLACE. That too wouldn't drop (replace) tables that were not part of the source.

To avoid having tables in the target that were not contained in the source, people very often simply drop the entire database before importing the dump. But this is (when using the --clean option) not really required.


How to auto start instance of sql server 2012 localdb on startup? How do you auto start instance of sql server 2012 localdb on startup?

Create a Windows Service program that uses the instance.  Auto start your program.

Or, use SQL Server Express, which is also free, and can be setup to run at startup - this is the default behavior.


db2 database alias not listed via list database directory but still unable to create that database I am trying to create a database by logging on to the linux server on which the db2 server is installed, as the instance owner

db2 => list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/mylogin/sqllib
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

db2 => create database testdb2
SQL1005N  The database alias "TESTDB2" already exists in either the local
database directory or system database directory.
    db2 => list database directory on /home/mylogin/sqllib

 Local Database Directory on /home/mylogin/sqllib

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Database directory                   = SQL00001
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Home
 Catalog database partition number    = 0
 Database member number               = 0
db2 => quit

I also checked if there are any other instances that exist other than mine, but there aren't.

DB20000I  The QUIT command completed successfully.
-bash-4.1$ db2ilist
mylogin

While the alias testdb2 is not listed by list database directory , db2 claims that this alias already exists in either the system database directory or the local database directory.

If this is true, how do I get the equivalent of list database directory that lists testdb2 as well?

db2 list db directory is for the system level directory. There is also db2 list db directory on <path> which lists any databases on that particular path.

Wondering if you have tried to uncatalog or catalog it first? You could see if cataloging it allows you to drop it.

Worse case you may need to locate the spot on disk with the database and remove it manually (as long as it isn't cataloged).


Get data from both tables what join should I use I have T1 as table 1

----------------
field1 | field2
---------------
 val1  | val2

I have T2 as table 2

----------------
field1 | field2
---------------
 val11  | val22

I want to write a query which can produce result like this

 field1 | field2 | T1.T2 |
 ------------------------|
 val11  | val22  | T2    |
 val1   | val2   | T1    |

Simply, you can use a CTE or SubQuery then ORDER BY [T1.T2] DESC as

WITH CTE AS
(
  SELECT Col1, Col2, 'T1' [T1.T2]
  FROM T1
  UNION
  SELECT Col1, Col2, 'T2'
  FROM T2
)
SELECT *
FROM CTE
ORDER BY [T1.T2] DESC;

Live Demo


Find termination and rehire dates We have a time history table named PRTH that has EmployeeID, PostDate (this is the day worked), and hours. Our ERP system does not track termination and rehire date history so I have been asked to look at the PRTH table to determine when an employee was terminated and rehired based on days without hours.

For example, if the last time I had hours was 1-1-17 and I had hours again on 3-1-17 then it should show my term date as 1-1-17 and rehire as 3-1-17. This appears to be an island and gap issue but I am drawing a blank.

    SELECT Employee, PostDate
    FROM dbo.PRTH
    WHERE Employee BETWEEN 400 AND 450
    ORDER BY Employee, PostDate

Employee    PostDate
400 2013-09-30 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00

The sample data above should be divided into 3 groups: (2013-09-30, 2013-09-30), (2013-10-15, 2013-10-15) and (2013-10-30, 2013-10-31). If employee 400 worked 1-1-2015 and the next date he worked was 5-1-2016 then we would want to see emp 400, term 1-1-2015, hired 5-1-16. Then if he had another gap in time history we would want another line with those and so on throughout all of our employees.

I have created a master calendar and tried the below statement but it has millions of rows. If I could only capture each term and end date instead of every day without pay that would be great.

WITH CTE_GAPS AS
(SELECT Employee,
    LAG(P.PostDate) Over(Partition by P.Employee Order By    P.Employee,P.PostDate) GapStart ,
    PostDate GapEnd,
    (DATEDIFF(DAY,LAG(P.PostDate) Over(Partition by P.Employee Order By P.Employee,P.PostDate),P.PostDate)-1) GapDays
FROM dbo.PRTH P)

SELECT  P.Employee,
    C.Calendar_Date
FROM Master.dbo.Calendar C CROSS JOIN CTE_GAPS P
WHERE
GapDays>29 AND
C.Calendar_Date
    BETWEEN DATEADD(DAY,1,P.GapStart ) AND DATEADD(DAY,-1,P.GapEnd)
    GROUP BY P.Employee, P.GapStart, C.Calendar_Date

I believe I was right there at the beginning. Here is what I ended up using. Please advise if anyone sees any improvements. Thanks!

WITH CTE_GAPS AS
(SELECT P.Employee, pp.FirstName, pp.LastName,
    LAG(P.PostDate) Over(Partition by P.Employee Order By
P.Employee,P.PostDate) GapStart ,
    PostDate GapEnd,
    (DATEDIFF(DAY,LAG(P.PostDate) Over(Partition by P.Employee Order By
P.Employee,P.PostDate),P.PostDate)-1) GapDays
FROM dbo.PRTH P INNER JOIN dbo.PREH pp ON P.PRCo = pp.PRCo AND P.Employee =
pp.Employee)

SELECT  P.Employee,
P.FirstName,
P.LastName,
P.GapStart,
P.GapEnd
FROM Master.dbo.Calendar C CROSS JOIN CTE_GAPS P
WHERE
GapDays>29 AND
C.Calendar_Date
    BETWEEN DATEADD(DAY,1,P.GapStart ) AND DATEADD(DAY,-1,P.GapEnd)
    GROUP BY P.Employee, P.GapStart, P.GapEnd, P.FirstName, P.LastName


Designing a Products database: One Products table or many separated by vendor? In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database.

My data is in spreadsheets because it was (somewhat) easy to export a tab-delimited text upload file for both the Amazon and Google e-commerce platforms.

But I've realized that I can achieve the same simple exporting goal through a relational database, minus the inconsistency headaches and plus the added benefit of an expressive query language.

Now my main question is how I should design my database; and, namely, how many tables my Products database should contain. I see three options here:

1 table for all products. This seems to me like it would avoid redundancy, and thus feels like the best choice. Take an example: if I decide to add an attribute to the database at a later date, I would not have to do so for each vendor/category (see option 2 and 3). I am inclined to say that there is a downside because products of one category will have many NULLs in attributes meant for other categories, but I'm not sure if this is actually a negative.
1 table for each vendor and its products. This was my instinctive first choice, but I don't find it to be the most logical division: what's critical in determining the uniqueness of a product's attributes is its category, not which vendor it was purchased from. Moreover, even though one vendor will often specialize in a certain kind of product, there will be category overlap if a vendor sells more than one kind of product.
1 table for each kind of product (e.g. padlocks, chain, safety equipment). I think this is the most reasonable choice other than number 1, because it is almost a guarantee that one kind of product will require different attributes than another. The obvious con I see to this strategy is the difficulty of making the divisions. I'll take hard hats as an example. Should hard hats have their own table? Certainly not. (Unless of course your business is in hard hats.) So, moving up the category chain, should construction accessories have its own table? Maybe closer. How about safety apparel? That makes sense to me, but then there will be the same problem of the many NULLS as option 1 because hard hats will be lumped in with safety vests and goggles etc. etc.

Hopefully I'm not way off the mark with my suggestions. I didn't want to ask before giving my questions some thought, but I'm new to databases, so I'm sure I didn't make the most informed reasoning. I'm heavily leaning towards option number 1, but I'd love to hear advice on any, or if I missed an obvious strategy entirely.

I would go with number 1. Having a separate table for each vendor or each product type will be a nightmare when trying to run reports that need data from multiple product tables. You don't want to have to create a new table each time you add a vendor or product type.
I understand your inclination coming from a spreadsheet, but with the ability to query the records easily, separating the products into multiple tables is not the way to go.

  I am inclined to say that there is a downside because products of one category will have many NULLs in attributes meant for other categories, but I'm not sure if this is actually a negative.

To prevent this, attributes should be in a separate table.

For example, a partial table definition may be as follows:

products
-- id
-- name

product_attributes
--product_id
--name
--value

attributes.product_id is a foreign key of products.id.

To further normalize it you could define the attributes in a separate table and have an attribute_id field instead of attribute_name:

    attributes
    --id
    --name

    product_attributes
    --attribute_id (foreign key of attributes.id)
    --value

(edited for spelling)


Cassendra schema design suggestion for chat archive I have gone though this article and here is the schema I have got from it. This is helpful for my application for maintaining statuses of a user, but how can extend this to maintain one to one chat archive and relations between users, relations mean people belong to specific group for me. I am new to this and need an approach for this.

Requirement :

I want to store messages between user-user in a table, whenever a user want to load messages by a user I want to retrieve them back and send it to user. I want to retrieve all the messages from different users to the user when user has requested. And also want to store class of users. I mean for example user1 and user2 belong to "family" user3, user4, user1 belong to friends etc... This group can be custom name given by the user.

This is what I have tried

CREATE TABLE chatarchive (
   chat_id uuid PRIMARY KEY,
   username text,
   body text
)

CREATE TABLE chatseries (
username text,
    time timeuuid,
    chat_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time ASC)

CREATE TABLE chattimeline (
    to text,
username text,
    time timeuuid,
    chat_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time ASC)

Below is the schema that I currently have,

CREATE TABLE users (
   username text PRIMARY KEY,
   password text
)

CREATE TABLE friends (
    username text,
    friend text,
    since timestamp,
    PRIMARY KEY (username, friend)
)

CREATE TABLE followers (
    username text,
    follower text,
    since timestamp,
    PRIMARY KEY (username, follower)
)

CREATE TABLE tweets (
    tweet_id uuid PRIMARY KEY,
    username text,
    body text
)
CREATE TABLE userline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC)

CREATE TABLE timeline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC)

I created a chat program a while back which is available on github

https://github.com/akc42/MBChat

which has some of the characteristics you are talking about.  In particular it provides both rooms which people can enter and discuss on an open way, and whisper boxes where a few people can get together and discuss things.

When people enter a room, or join a whisper, they can see a little bit of the past conversation.

Before explaining the schema a bit more, it is worth just pointing out that I have two methods of validating users.  This is because the normal use is a chat extension to an smf forum - so users are already logged in to the forum, and they carry their same identity over to chat (and have some characteristics that are derived from membership groups in the forum).  However so chat can be used stand alone without a forum, there is a separate user database which can be queried during the logon phase which identifies the users and their capabilities.

So first that database is at inc/user.sql in the repository.

BEGIN;

CREATE TABLE users (
  uid integer primary key autoincrement NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role text NOT NULL DEFAULT 'R', -- A (CEO), L (DIRECTOR), G (DEPT HEAD), H (SPONSOR) R(REGULAR)
  cap integer DEFAULT 0 NOT NULL, -- 1 = blind, 2 = committee secretary, 4 = admin, 8 = mod, 16 = speaker 32 = can't whisper( OR of capabilities).
  password character varying NOT NULL, --raw password
  rooms character varying, -- a ":" separated list of rooms nos which define which rooms the user can go in
  isguest boolean DEFAULT 0 NOT NULL
);
CREATE INDEX userindex ON users(name);
-- Below here you can add the specific users for your set up in the form of INSERT Statements

-- This list is test users to cover the complete range of functions. Note names are converted to lowercase, so only put lowercase names in here
INSERT INTO users(uid,name,role,cap,password,rooms,isguest) VALUES
(1,'alice','A',4,'password','7',0),
(2,'bob','L',3,'password','8',0),
(3,'carol','G',2,'password','7:8:9',0),
(4,'dave','H',0,'password','10',0),
(5,'eileen','R',8,'password','',0),
(6,'fred','R',16,'password','',0),
(7,'gail','R',0,'password','',0),
(8,'harry','R',0,'password','',1),
(9,'irene','R',32,'password','',0);

COMMIT;
VACUUM;
-- set it all up as Write Ahead Log for max performance and minimum contention with other users.
PRAGMA journal_mode=WAL;

However once they have been logged on, these credentials are copied over into the actual chat database that is being used

The following are elements of the schema (taken from data/chat.sql file from that repository) that might help you.

Firstly the the open rooms (actually not all rooms are equal, there is some limitations - see comments)

CREATE TABLE rooms (
  rid integer primary key NOT NULL,
  name varchar(30) NOT NULL,
  type integer NOT NULL -- 0 = Open, 1 = meeting, 2 = guests can't speak, 3 moderated, 4 members(adult) only, 5 guests(child) only, 6 creaky door
) ;

INSERT INTO rooms (rid, name, type) VALUES
(1, 'The Forum', 0),
(2, 'Operations Gallery', 2), --Guests Can't Speak
(3, 'Dungeon Club', 6), --creaky door
(4, 'Auditorium', 3), -- Moderated Room
(5, 'Blue Room', 4), -- Members Only (in Melinda's Backups this is Adults)
(6, 'Green Room', 5), -- Guest Only (in Melinda's Backups this is Juveniles AKA Baby Backups)
(7, 'The Board Room', 1), --various meeting rooms - need to be on users room list
(8, 'Marketing', 1),
(9, 'Engineering',1),
(10, 'IT Dept', 1),
(11, 'Finance', 1);

Now users themselves are carried over from the previous database (or from the forum and have a matching table)

CREATE TABLE users (
  uid integer primary key NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL default 'R',
  rid integer NOT NULL default 0,
  mod char(1) NOT NULL default 'N',
  question character varying,
  private integer NOT NULL default 0,
  cap integer NOT NULL default 0,
  rooms character_varying
);

But this is not all - I have another concept - which is that of a Participant in a conversation - with a conversation being just an ID.

CREATE table wid_sequence ( value integer);
INSERT INTO wid_sequence (value) VALUES (1);

CREATE TABLE participant (
  uid integer NOT NULL REFERENCES users (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  wid integer NOT NULL,
  primary key (uid,wid)
);

Not there is a wid_sequence table which my code manually increments when anyone creates a new conversation - (I call it a whisper box, but sometimes it becomes what I call a private room ), and then a record in the participant table whilst the conversation is in place.

Finally, so I have an archive of messages - which I can reference and play back at a later date (either a short list of recent messages when someone enters a room, or someone with the secretary capability can enter a room and obtain a printout of the entire conversation - as minutes of a meeting for instance).

I think it is this table that is your archive that you mention in the question

The schema for this is

CREATE TABLE log (
  lid integer primary key,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  uid integer NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL,
  rid integer NOT NULL,
  type char(2) NOT NULL,
  text character varying
);

The important item here is the type field.  It is in essence a replay of the actions as well as the conversation, so we also can replay new conversations starting up (where rid then maps to the wid created from wid_sequence), and people entering and exiting rooms etc.

I trust that gives you an idea how you can proceed.  Feel free to examine my code on github and ask any questions that you might have of it.

As a postscript, there is a table I didn't mention called 'parameters' which controls a lot of how things work.  One of those is whether encryption needs to be used.  It is beyond the scope of this answer to go into that - as it is fairly complicated - but you may need to consider how private you want private conversations to be and whether or not they should be stored encyrpted in your archive.


Error changing mysql 5.6 default port I've changed my.ini with port=3308 but after restart the service mysql 5.6 is working in the same default port 3306.

I've tried in mysql 5.5 and it worked fine.

C:\Archivos de programa\MySQL\MySQL Server 5.6

my.ini

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
port = 3309
# server_id = .....

What am I missing? Is there any bug?

EDIT BY @RolandoMySQLDBA

Please run the following

cd C:\Archivos de programa\MySQL\MySQL Server 5.5
dir
cd C:\Archivos de programa\MySQL\MySQL Server 5.6
dir

and post the output in this question

MySQL 5.6 my.ini file is in the path where the data is stored, in my case

C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6

instead of the path where mysql is install

C:\Archivos de programa\MySQL\MySQL Server 5.6

, like older versions.

We have to change C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6 \my.ini file to the changes take effect, and restart the service.

On Windows, MySQL programs read startup options from the following files, in the specified order

More info Using options files

Modifying Registry

As well it is possible to do it in the registry with regedit command

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

and change binPath

MySQL commands

You can see configuration options through the following command and look up 'Default options' to see the paths where configuration .ini and .cnf are.

> mysqld --verbose --help

And with this other command you can see mysql variables

> mysqladmin variables

Command line

You can also see the default my.ini file that mysql is using by looking at the service properties, in executable path on option --defaults-file.

It is possible to change service through command line
You can use the sc config command to change the path a service points to:

SC CONFIG YourServiceName binPath= "C:\SomeDirectory\YourFile.EXE"

This will update the service called YourServiceName and change the "Path to Executable" entry to C:\SomeDirectory\YourFile.EXE. You will want to restart your service afterwards, which you can do with:

NET STOP YourServiceName & NET START YourServiceName


Which is better: Selecting across linked servers vs migrating the database I have a customer that needs to query a linked server to produce a report. They will be joining a linked server table with a local table.

Is it better to just let them do the select across the linked server OR migrate (backup/restore) the database every day and have there query run against 2 local databases?

For discussion sake let's say the query returns 20K records.

You'll likely get the best performance by copying the content of the remote table locally first, then running the query.  Especially if you filter the remote table through a remote view or a stored proc on the remote server.

Joining across a linked server can be brutal on performance depending on the implementation details of the query.


SSRS shared data sources and shared schedule I am cleaning up our existing SSRS (report server) and would like a few suggestions and best practices in terms how it should be used. Currently each report has a connection and schedule. Would it be a good practice if I created a few shared schedules? What would be the impacts in terms of performance and resources? Also, would there be any impacts if I were to create a Data Source is SSRS and use that particular shared data source in reports? Also, if lets say 10 reports are scheduled to run at 1:00PM , would they queue or would they execute all at once?

SSRS has a number of configuration files, including RSReportServer.config.

Both Shared Schedules and Shared Data Sources are good things to create since they help you manage your configuration without giving each and every report it own schedule. View this as saving you some work.  Of course, independent Schedules and Data Sources will likely be needed for some reports.

Since you are concerned with overall load, note that the MaxQueueThreads configuration will control how many parallel reports can run at once.  0 means run as many as possible, but some other number (e.g. 3) will limit Reporting Services to run that number reports at one time.

If you have 10 reports on the same schedule the other reports will run as Queue Threads open up for use.

To configure appropriately need to know your resources available and configure appropriately.  Probably there will be a little trial and error until you get things balanced.


You can use ROW_NUMBER() or RANK() setting a partition by name and ordering by datetime.

Then fetch these rows with a row number >= 2.

create table books (id int, title text);
insert into books values (1, 'title1'), (2, 'title2');

create table events(id int, title_id int, name text, datetime timestamp);
insert into events values
(1, 1, 'event1', current_timestamp),
(2, 1, 'event1', current_timestamp - interval '30 minutes'),
(3, 2, 'event2', current_timestamp),
(4, 2, 'event2', current_timestamp - interval '5 minutes'),
(5, 2, 'event3', current_timestamp);

with x as
(
    select id, title_id, name, datetime,
           row_number() over (partition by name order by datetime) rn
    from   events
    where  datetime >= current_timestamp - interval '5 minutes'
)
select   max(rn) as id, name
from     x
where    rn >= 2
group by name;

id | name
-: | :-----
 2 | event2

dbfiddle here


Migration Wizard Offline SQL Server to Oracle, before Move Data, directory Data is empty I'm migrating data that be in SQL Server to Oracle

I'm doing it Offline, I generated .ocp, I ran the OMWB_OFFLINE_CAPTURE.bat, I passed the parameters and generated two directories, one called Master and other with name of my Database in SQL Server, in that case called db_GFA and I granted privileges to user

After I executed the Migration Wizard Offline, I catched the sqlserver2008.ocp file, I Converted types data and didn't traslate the Objects, because I want just the data, and I executed the Move Data e generated the directory called DATAMOVE, but the directory "Data", in DATAMOVE, is empty, didn't brought the data

Someone can help me? I need that data

I didn't see a thing

After you executed Move Data, remember that Migration is between SQL Server to Oracle, the SQL Developer generate some scripts and one of that scripts generated, call MicrosoftSQLServer_data.bat, you must run that script and it'll bring the data to you complete the Migration


MySQL: Reference aggregate function in conditional select I'd like to reference aggregate function like this:

Sample Data:

+-----------+----------+-------+
| member_id | group_id | score |
+-----------+----------+-------+
|         1 |        1 |    12 |
|         2 |        1 |     2 |
|         3 |        1 |    20 |
|         4 |        2 |    40 |
|         5 |        2 |     4 |
|         6 |        3 |    19 |
|         7 |        3 |    17 |
+-----------+----------+-------+

Query:

SELECT MAX(IF(score = MAX(score), member_id, NULL)) AS member
FROM members
GROUP BY group_id

Expected Result:

+-----------+
| member_id |
+-----------+
|         3 |
|         4 |
|         6 |
+-----------+

Fiddle: http://sqlfiddle.com/#!9/08af68/2

But this results in the error:

  Invalid use of group function

I've simplified things here but I'm trying to use this in a much more complex scenario where the score value is the result of a large complex query.  Doing this the standard way which I'm aware of results in something that looks a bit ridiculous since the initial query to get the working values is upwards of 35 lines of code.

QUESTION: Why are aggregate functions not allowed to be used like this?  Is there a workaround that doesn't involve effectively duplicating the entire SELECT simply to get the MAX() value for use as a point of reference?

Cleaver.  But no go.

To achieve what you ask for would take two passes over the data.  Things don't work that way.

The first pass is to find the max for each group; the second pass is to check all the value against the maxes.

You can do it with a subquery, an @variable or several other ways.  Follow the tag to see ways to do "groupwise max".


Can I Make This Query Faster? I have this SQL query:

SELECT parent_category FROM category
JOIN
(
    SELECT category_id FROM venue
    JOIN
    (
        SELECT venue_id FROM city_venues
        WHERE city_id = 3
    ) AS venues
    WHERE venues.venue_id = venue.venue_id
) AS venue_categories_list
WHERE venue_categories_list.category_id = category.category_id;

This command gives me exactly the results I want but I have noticed that it's execution is very slow (100-200ms) even with small datasets. Since I want this query to be part of a trigger, I want it to be as efficient as possible. Any ideas on how to make this faster ?

EDIT:

The tables related to this query are the ones below:

CREATE TABLE queries:

-- -----------------------------------------------------
-- Table `app`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`category` (
  `category_id` CHAR(24) NOT NULL,
  `category_name` VARCHAR(50) NOT NULL,
  `category_plural_name` VARCHAR(50) NOT NULL,
  `parent_category` CHAR(24) NOT NULL,
  `url_icon` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`category_id`),
  CONSTRAINT `fk_parent_category`
    FOREIGN KEY (`parent_category`)
    REFERENCES `app`.`category` (`category_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `app`.`venue`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`venue` (
  `venue_id` CHAR(24) NOT NULL,
  `venue_name` VARCHAR(100) NOT NULL,
  `address` VARCHAR(100) NULL,
  `lat` DECIMAL(16,14) NOT NULL,
  `lng` DECIMAL(16,14) NOT NULL,
  `category_id` CHAR(24) NOT NULL,
  PRIMARY KEY (`venue_id`),
  INDEX `fk_venue_category_idx` (`category_id` ASC) VISIBLE,
  CONSTRAINT `fk_venue_category`
    FOREIGN KEY (`category_id`)
    REFERENCES `app`.`category` (`category_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `app`.`city`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`city` (
  `city_id` INT NOT NULL AUTO_INCREMENT,
  `city_name` VARCHAR(50) NOT NULL,
  `lat` DECIMAL(16,14) NOT NULL,
  `lng` DECIMAL(16,14) NOT NULL,
  PRIMARY KEY (`city_id`),
  UNIQUE INDEX `city_name_UNIQUE` (`city_name` ASC) VISIBLE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `app`.`city_venues`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`city_venues` (
  `city_id` INT NOT NULL,
  `venue_id` CHAR(24) NOT NULL,
  PRIMARY KEY (`city_id`, `venue_id`),
  INDEX `fk_venue_idx` (`venue_id` ASC) VISIBLE,
  CONSTRAINT `fk_hasVenues_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `app`.`city` (`city_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_hasVenues_venue`
    FOREIGN KEY (`venue_id`)
    REFERENCES `app`.`venue` (`venue_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

I use MySQL Workbench to generate the queries for creating the schema. I use InnoDB as the engine.

EXPLAIN Output:

What I want to do with this query is to return all parent categories IDs' for each venue a city has. I care about duplicates as I want to count how many venues of each category does a city have. I would appreciate if you could give me hints about how I can do multiple counts also ( return columns with each category metrics).

Here are some example queries that simplify the SQL, as well as provide solutions to what you are asking.  As far as performance, you'll have to test them:

--RETURNS ALL PARENT CATEGORIES FOR A CITY
SELECT cats.parent_category
FROM city_venues AS venues
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3

--COUNTS HOW MANY CATEGORIES IN EACH CITY
SELECT venues.city_id, COUNT(cats.parent_category)
FROM city_venues AS venues
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3
GROUP BY venues.city_id

--COUNTS THE VENUES IN EACH CATEGORY FOR A CITY
--(Specified in the WHERE     statement)
--REMOVE THE "WHERE venues.city_id = 3" TO GET OVERALL
--COUNT OF VENUES IN ALL CITIES
SELECT venues.city_id, cats.parent_category, COUNT(venue.venue_id)
FROM city_venues AS venues
     JOIN venue ON venue.venue_id = venues.venue_id
     JOIN category AS cats ON venue.category_id = cats.category_id
WHERE venues.city_id = 3
GROUP BY venues.city_id, cats.parent_category


Will a SP which writes records fail if accessed by a db_datareader role? I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that.

Recently there has been some talk of it maybe running slowly - I'm pretty confident that there is no reason for the SP to be running slowly. But I added a log table to the DB and made the SP write to it on entry and again after the SELECT is complete so I can see how long the calls are taking.

This change works fine for my Windows Authentication log-in - and it also works fine when I use the application log-in which the remote app is using. However I am doing the connection from the .Net environment and not from Java.

When I changed the SP to do the logging I noticed that the IDs I was INSERTing into the log-table were jumping - indicating that failed INSERTs had been taking place - presumably caused by the remote-app (as that is the only thing that uses this SP).

The application log-on has only db_datareader role assigned to it. I have GRANTed EXEC to the application log-on of course.

Is it the case that the SP will not be able to INSERT a row because the accessing log-on has only db_datareader? And if so, how come the SP does the INSERT when I use that same log-on?

If the same user owns both table and stored procedure (usually dbo), then permissions are not checked. This is known as "ownership chaining".

So you could have a DENY on the table and it won't be checked. db_datareader makes no difference: it isn't checked.

If the owners are different (and note that it's the owner of the schema that matters) then rights will be needed. See the MSDN example above.

Anyway, you need to catch the errors to make sure, either by TRY/CATCH or via SQL Profiler


Delete or hide the row in set text in mysql result Is there a way to not show the text row in set (0.10 sec) in the result of a mysql query

Answered in a comment by danblack:
mysql -B --skip-column-names -e "SHOW VARIABLES LIKE '%verbosity%'"


Filtering a table based on multiple values of a single column I have a list of names in a specific column of a table. Is there a way to build a query which can pull out all the attributes of this names? I am a total novice so any help is appreciated

Example: User table

table columns:

User id
attributes
user privileges
1st name
2nd name

The filter has to be applied based on a bunch of user first names.

Sure, you are looking for the IN condition.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm

SELECT UserID, Attributes, UserPrivileges, 1stName, 2ndName
FROM User
WHERE 1stName IN ('John', 'Jane', 'Joseph', 'Laura');


Add header to multi server or instance query (powershell) I have the following PowerShell script I am looking to add a header to this results without having a duplicate per each iteration but I am not familiar enough with Powershell if anyone can point me in the right direction it would be helpful.

  $servers = 'E:\DBA\servers.txt'
  $outfile = 'E:\DBA\out.csv'
  $queryfile = 'E:\DBA\query.sql'

  Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File
  $outFile -Force} Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

The error is

If you just want to hard-code the header, you can use the Powershell New-Item cmdlet.  I used the -Force parameter to initialize the outfile with the single header row and the rest of your script uses -Append to add the detail information.

New-Item $outfile -value "Header,Header,Header,Header`n" -Force
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}

If you want dynamic columns generated based on the actual query, you can run an initial Get-Content using -First 1 (to only get the first server) and limit the output to the first row only by using -First 1.  Then, follow up with your usual Powershell script.

Get-Content $servers -First 1 | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -First 1 | Out-File $outFile -Force}
Get-Content $servers | ForEach-Object {Invoke-SqlCmd -inputfile $queryfile -ServerInstance $_ -Database master | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append $outFile}


Name of not null constraint is missing in error message The relation was created in Postgres 9.6 with use of the following commands:

 CREATE TABLE import_event (
   import_event_id BIGINT NOT NULL,
   filename VARCHAR (200) CONSTRAINT NN_import_event__filename NOT NULL
 );

 ALTER TABLE ONLY import_event ADD CONSTRAINT PK_import_cak_key PRIMARY KEY (import_event_id);

Notice that not null constraint is named.
Then the following command was executed

 insert into import_event (import_event_id) values (1);

Of course, the constraint violation error was raised. The content of the message is as follows

 ERROR:  null value in column "filename" violates not-null constraint
 DETAIL:  Failing row contains (1, null).**strong text**

The problem is that the name of not null constraint is missing in that message. However, the correct error message:

 ERROR:  new row for relation "import_event" violates check constraint "nn_import_event__filename"

is produced when not null constraint is declared as follows

 ALTER TABLE ONLY import_event ADD CONSTRAINT NN_import_event__filename CHECK(filename IS NOT NULL);

Is that a bug in Postgres 9.6 or is there a problem with constraint declaration?

So far as I can tell the bit that goes CONSTRAINT NN_import_event__filename is just ignored.

the name nn_import_event__filename is not present when I dump the database, only the not-null attribute remains.

This does not match the the example given in the documentaion  which claims that it is possible to name a not null constraint.

  Define two NOT NULL column constraints on the table distributors, one of which is explicitly given a name:

CREATE TABLE distributors (
   did     integer CONSTRAINT no_null NOT NULL,
   name    varchar(40) NOT NULL
);

So, you've found a bug.  if you report it it will probably be corrected fairly quickly, but the fix may be merely an update to the documentation.


Log and Data Drive Configuration in VM environment Anyone have any good feeling on how to arrange the underlying disk configuration for a VM (Hyper-V) implementation of SQL Server vs installing straight up on the hardware?

For a native Install, best practices say to put the logs on separate spindles from the data spindes, but what about in a virtual environment? Whereas the VM has virtual disks, we could create separate log .vhd and data .vhd and put those on separate spindles as before, but why not just stripe all your available disks and put the log and data virtual drives on the same set? Hypothetically, the more spindles in the stripe the faster the response and more IOPS, so we could just put both files on the same set. The Host machine would be acting as sort of a SAN.

The server we have is moderately busy, no problem with IO performance (logs and data currently on separate spindles) or CPU or memory pressure. The goal is to have more flexibility in maintenance, being able to clone or move the VM elsewhere, and possibly put another VM on the box to better utilize the hardware. It seems that creating one large striped drive in the host machine would allow us the full use of all available drive space without much impact on performance. The alternative is losing out on about 1/3 drive space just to hold smallish log files.

Server config is Windows 2012 R2 OS, SQL Server 2016. 6 drives in RAID 10 for all of logs tempdb and data -OR- 2 drives in RAID 1 and 4 drives in RAID 10 for tempdb and data.

Much thanks!

It really depends on what's best for your situation as long as you follow some guidelines.  We keep the disks for transaction logs separate because often RAID 6 doesn't give us the IOPS we want during peak usage, but many times it does.  It depends on your peak IOPS requirements, future growth, and file management. Note that these best practices were created when 7200 and 10k RPM magnetic disks were still expensive.

Isolation, Throughput, Management, and Tail Of Log Backups

One issue with putting it all on 1 big array is that different workloads will now affect your log files where before it wouldn't.  If someone does a SELECT * on a huge table and it has to load it into memory, you're fighting for resources off the disk now. All of a sudden you have a different workload when you're trying to troubleshoot what happened.

However, Perhaps you have really good SSDs and your peak writes won't really matter.  Another item is you could configure write cache on different arrays so you could dedicate your entire cache to log files.  Again, if you're on SSDs on your workload, or you don't have those caching options that might not matter.  Another reason to have your own array was to short stroke the disks, that was very helpful for magnetic disks.  Remember that data fragmentation could affect the log file too.  Sure you could have VLFs (logical fragmentation) taken care of easily but what happens when the underlying disk decides to move data around or you get fragmentation due to how the files are growing?  In many cases that won't cause a problem, and in some it will.

Also, note that if anything happens to that array, physical OR logical, you can kiss tail of the log backups goodbye.  If your data loss requirements are OK with that then make sure to get that in writing.


Dynamic pivot: sum of sales per month My table is like this:

Sitecode    Month  Amount
--------    -----  ------
XX          Jan     1000
XX          Jan     3000
XX          Apr     3000
XX          Apr     1000

What I want is, to show the result something like this:

Sitecode    MonthJAN   MonthAPR
--------    --------   --------
XX          4000       4000

As others have said this is known as a PIVOT. There are several ways in which you can transform your data from rows into columns of data.

If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT function you would use an aggregate function with a CASE statement.

Aggregate/CASE Version:

select sitecode,
  sum(case when [month] = 'Jan' then amount else 0 end) MonthJan,
  sum(case when [month] = 'Apr' then amount else 0 end) MonthApr
from yourtable
group by sitecode;

See SQL Fiddle with Demo.

The PIVOT function was made available in SQL Server 2005, so if you are using that version or a newer one then you can apply that to your data.

Static PIVOT:

select *
from
(
  select sitecode,
    [month],
    amount
  from yourtable
) src
pivot
(
  sum(amount)
  for month in (Jan, Apr)
) piv;

See SQL Fiddle with Demo

The above two versions work great if you know the values ahead of time. If not, then you will use dynamic sql to create the result.

Dynamic PIVOT:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month)
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT sitecode,' + @cols + ' from
             (
                select sitecode, [month], amount
                from yourtable
            ) x
            pivot
            (
                sum(amount)
                for month in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

All 3 versions of this will return the same result:

| SITECODE |  JAN |  APR |
--------------------------
|       XX | 4000 | 4000 |


simple query is not using indexes My query is not using indexes.
it was taking around 5 seconds.
output is 500 rows only. it is a simple query

Please find the explain plan and table structure here.

Explain Plan

mysql> explain SELECT COUNT(*) AS `records_found`
               FROM `builds` AS `build`
               WHERE (`user_id` = '32762'
               AND `country_id` = 'gb'
               AND `share` = 0
               AND `mode` = 'bouquet'
               AND `build_data` != '');

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | build | ALL  | NULL          | NULL | NULL    | NULL | 35209 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Table structure

mysql> show create table builds\G
*************************** 1. row ***************************
       Table: builds
Create Table: CREATE TABLE `builds` (
  `build_id` varchar(8) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `derivation` int(11) NOT NULL,
  `share` tinyint(4) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `build_data` text NOT NULL,
  `mode` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`build_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

According to your table structure, you only have a PRIMARY KEY on build_id.

Thus, there is no index that can be utilized to solve this query. I suspect you are very new to indexes; having an index on some column doesn't mean all queries will run faster. You need an index that matches you query.

For example, in your case, an index on user_id or an index on country_id or an index on share or an index on mode would work; Indexes on combinations of the above, like INDEX (user_id, country_id) would do well, too.

Which should you choose? One that would significantly limit the number of rows to "search by hand" (so to speak), and one that would do well for your other common queries.

My recommendation to you is to study how indexes work in relational databases.


SQL Server 2017 CU3 will not add read-scale replica. Thinks there is a cluster Using SQL Server 2017, CU3. I successfully create a 2 node cluster=NONE availability group. Both replicas are VMs local to the site. When attempting to add another replica that is off-site, I get sql error 19417, which basically says my replica add failed because the operation is not supported when the AG is running under a Windows Server Failover Clustering context. However, I am not using a cluster, that is the point of the cluster=none AG. I have had trouble before with CU1, and went backwards to the RTM version. That is my next step. Posting in case someone has seen something similar.

I finally found Creating a clusterless availability group using windows server 2016 and sql server vNext CTP 1.4 on Stack Overflow, which got me on the correct path.

I had been using the wizard to create the AG, when that failed I used the wizard to script out the t-sql and run step by step. What I failed to notice is that the wizard creates the replica on the primary, but never actually joins the replica on the secondary.

That is, this command executes on the primary:

ALTER AVAILABILITY GROUP [XXXXX]
ADD REPLICA ON N'YYYYY' WITH (other stuff)

but this command never runs on the new secondary:

ALTER AVAILABILITY GROUP [XXXXX] JOIN WITH (CLUSTER_TYPE = NONE);

And apparently the cluster_type = none is critical. At any rate, I am watching my clusterless AG successfully build now, so woohoo! And maybe worth mentioning that I am using SSMS 17.5. The wizard code seems to need an update to include the missing step.

As a follow up. I worked with MS through my company's support agreement. They verified there were a few scenarios that exhibited the wizard behavior described above. They are targeting SSMS 17.6 to incorporate the updates.


Seamless SQLServer Database Name Change I need to change the name for a few databases, however the databases are used by a lot of applications managed by several different teams. It would take some time for all the applications to finish changing connections and database name references. Is there a way to allow the change to be done seamlessly? Was looking at synonyms to temporarily allow references to the renamed database but unfortunately it is only for database level.

Create an "alias" database with the name you want to use.
Create synonyms for the objects by querying the system tables to create a simple script, here for example for user tables.
select
  'Create Synonym '
   +quotename(object_schema_name(id, db_id()))+'.'
   +quotename(o.name)+
   ' For [SourceDB].'
   +quotename(object_schema_name(id, db_id()))+'.'
   +quotename(o.name)+';'
from sysobjects o where type = 'U'


Structure of database with billions of rows of data I have started working on a system setup, that includes a database, that collects around 6-7 billions of rows of data each year.
I'm abit confused over the design of this database, and I'm not able to discuss it with the designer of this database.

Its a factory, that has alot of high interval sensors monitoring all kinds of things.

Each year they create a new database, to recieve the data from that year.
So they have db names like this:

  FactorySensors2017

  FactorySensors2018

In addition to yearly databases, they also create daily tables, for the sensors data, that looks like this:

  SensorData01012018

  SensorData02012018

  SensorData03012018

They have a reporting tool, that fetches data for a timespan that they define, and for the sensors/sensortypes that they want, that then selects that data. So I'm guessing that this kind of structure with the daily tables, will make the querys faster, since it has to scan less rows.

It is however in big contrast to what I know about database normalization, and therefore I initially considered it bad practice. But because of the .bak files being smaller and more manageable with yearly databases, and the daily tables faster to fetch data from, I guess that there is some reasoning behind the choices.

Is this kind of design/structure a proper way of dealing with this amount of data? When loading times and .bak backupfiles are taken into consideration. Or should it have been dealt with in a different way?

Update:

The sensor data tables, are only being used for the initial insert of data, and then later on data will be fetched from them, with select statements.
There may be some rare cases of updating sensor data, if a sensor was malfunctioning, but the performance of these updates doesn't matter, since they will be rare.

The sensordata tables looks like this:

CREATE TABLE [dbo].[SensorData01012018](
    [RegisterId] [uniqueidentifier] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [AvgValue] [float] NOT NULL,
    [NoAggregations] [int] NOT NULL,
    [MinValue] [float] NOT NULL,
    [MinValueTime] [datetime] NOT NULL,
    [MaxValue] [float] NOT NULL,
    [MaxValueTime] [datetime] NOT NULL,
    [Value] [float] NULL,
    [ValueTime] [datetime] NULL
)

And an example of a select query, could look like this:

SELECT AvgValue, Value, ValueTime
FROM SensorData01012018
WHERE RegisterId = 367a5c7f-3c88-44f9-b3d8-7576556bc199
    AND StartTime >= 2018-01-01 3:30:00.000
    AND EndTime < 2018-01-01 18:30:00.000
ORDER BY StartTime ASC

This is a typical way to deal with this kind of data.

If you had a postgres database, you'd create one master table (to formalize the structure of the sensor data), and then create tables that inherit all the columns from the master table without adding any additional columns. This would create a partitioning of your data based on date / timestamp of the sensor data.

Querying the master table in this configuration means going through all the child tables and returning the approapriate answers. If one adds the "check" constraints for the child tables (corresponding to the temporal data in the tables), only the relevant children will be scanned.

See more e.g., https://www.postgresql.org/docs/10/static/ddl-partitioning.html

SQL Server 2014 seems to have a similar concept:

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes


Understanding WAIT_TIME_MS Test scenario:
I am only one user on database. I reset sys.dm_os_wait_stats. I run a SELECT query (table scan).

Result:
Execution time = 20 minutes, but sys.dm_os_wait_stats -> PAGEIOLATCH_SH -> Wait_time_ms = 40 minutes (2 580 252)

Question:
How is it possible that in 20 minutes: wait_time_ms > execution time ?

As the two comments mention, there are two things at work here.

sys.dm_os_wait_stats records accumulated wait times since the server was restarted. So if the server has been up for a while, these can be fairly high. To get a meaningful value out of sys.dm_os_wait_stats, you need to take two snapshots e.g. 5 minutes apart and compare them to each other. Keep in mind, there you cannot tie them to a single connection. Anything on the server that is waiting for something during that time will cause a change in one of the values returned by sys.dm_os_wait_stats.
If a query gets executed in parallel, each thread will use up CPU time as well as potentially wait time. So both the total CPU time as well as the total wait time for a single query can be higher than the elapsed time.


How Do I Find Who Last Edited a Record or Table in a Database? I believe that I have most of the work done for whoever wants to tackle this question.

I have the following query built:

USE MyDataBase;
GO

SELECT DB_NAME(ius.[database_id]) AS [Database],
       OBJECT_NAME(ius.[object_id]) AS [TableName],
       MAX(ius.[last_user_lookup]) AS [last_user_lookup],
       MAX(ius.[last_user_scan]) AS [last_user_scan],
       MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius

WHERE ius.[database_id] = DB_ID()

GROUP BY ius.[database_id], ius.[object_id];

If possible, how do I add which user it was that made a change?

You cannot achieve this if you do not have a historical or archive table that store this data. You have to create an AuditDb and create some DML trigger.

OR

You have to use a third-party log reader (if log is not shrinked) like ApexSQL LOG


Copying Oracle datafiles to a system clone We have an Oracle 11G R2 on Windows 2008 R2.

One night, a windows update package caused our Oracle Instance to be down (no way to connect using sqlplus. we believe it's a network, NIC issue.).

We have restored the system from an 36 hours old system snapshot. We do not use archive mode. So every single parameter file in oracle stays same, but datafiles are old.

Question is, can we shut the db off and copy the newer datafiles to the restored system? Is this operation safe?

Thanks in advance.

Of course you can, if you can cleanly shut down your old database and it is consistent. A little reading of the documentation or googele-fu would have helped. Searching this site would have provided this excellent answer.

But: what do you want to achieve? If it really is an issue with the patch and your hardware configuration, why do you expect your clone to be not affected?
Is only your SQL*Net communication not possible or does e.g. RDP not work, too?

So before cloning the database without knowing the root cause I would suggest some further investigation and searching MOS. If it really is an MS patch issue that stops SQL*Net from working, there will be almost certainly a note there.

Some peeking in your firewall configuration might help, too, perhaps it is a trivial think like the patch has (re)activated your internal windows firewall.


MySQL Handler_read_rnd_next == tablescans without indexes? I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance.
In the MySQL Handlers Plot, i have been noticing alot of "Handler_read_rnd_next" calls.

After doing some digging in the mysql dev docu, this handler supposedly indicates: "this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have"

I then started analyzing our queries for non-indexed calls with the help of tcpdump and mk-query-digest (awesome tool btw) with the options:

--filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")'

but am drawing a blank:
all the queries which are being listed as not using an index are ones which have a "derived" select type in them somewhere and all finish within a few milliseconds.

Hence my question: if the derived select types are the only ones without indexes, where are my Handler_read_rnd_next instances coming from? I'd appreciate any help i can get on trying to find out what the issue is.

Thanks!

You should enable slow-query-log and log-queries-not-using-indexes to in order to manifest what queries are skipping on the indexes.

This is just a personal preference, but I usually the --processlist option of mk-query-digest. I wrote a automation script for it long ago : MySQL general query log performance effects based on Percona's video about mk-query-digest being a replacement for  the slow log.

Once the queries are made known, you will have run EXPLAIN on all selects. Any query whose EXPLAIN plan fails to choose an index is guilty of doing the table scans.


Is splitting tempdb files across drives necessary? If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I want to persuade my manager to put all 4 tempdb files on one drive.
If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'? I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

Thanks

If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I suggest you to understand why we use multiple files for tempdb. Once you understand that clearly and know your underlying storage system (as suggested by comments) you will be able to answer the question. Things might vary depending on your SQL Server Version and major TempDB enhancements are done in SQL Server 2016, 2017 and 2019.

  Tempdb contention refers to a bottleneck for threads trying to access
  allocation pages that are in-memory; it has nothing to do with I/O.

Read more details about this here by Paul Randal.

Microsoft's official recommendation about reducing tempdb allocation contention is here:

Recommendations to reduce allocation contention in SQL Server tempdb database

If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'?

I am sure the answer can be depends on the type of corruption. But I do not see any relevance with this question and splitting your files into two LUNs.

I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

SQL Server will NOT automatically create new files, but you can do it yourself. Will it cause downtime?  It depends. Read this question and answer.

Tempdb Add files require Restart
Correctly adding data files to tempdb by Paul Randal


Avoid double query on the same table I have two queries which are doing basically the same thing, but have different grouping, the first query (query 1) is used to populate a chart, and the second to populate a table.

Query 1:

SELECT key_id
       ,sum(salary)
       ,sum(bonus)
       ,created_at
FROM table
WHERE emp_id = 1
GROUP BY key_id, created_at

Query 2:

SELECT key_id
       ,sum(salary)
       ,sum(bonus)
       ,count(*) OVER() AS full_count
FROM table
WHERE emp_id = 1
GROUP BY key_id

I have created a function that returns those two queries in a json format, chart: [...], table: [...]. The problem is that I need to query the same table twice, because of my grouping. Is there any way of dealing with this situations?

The new 9.5 version has added CUBE, GROUPING SETS and ROLLUP extensions to GROUP BY which can be used for such queries. If you have to do this in previous versions, one way is using a CTE, with something like (note that it's not the same exact output as your query, since I didn't know the type of created_at. I guess it's a timestamp and didn't want to mix it with the integer count):

WITH grp AS
  ( SELECT key_id,
           sum(salary) AS salary,
           sum(bonus)  AS bonus,
           created_at,
           count(*)    AS full_count
    FROM table
    WHERE emp_id = 1
    GROUP BY key_id, created_at
  )
SELECT *
FROM grp

UNION ALL

SELECT key_id,
       sum(salary),
       sum(bonus),
       NULL,
       sum(full_count)
FROM grp
GROUP BY key_id

ORDER BY key_id, created_at NULLS LAST;


How to find out all the permissions that belong to a specific server/ database role in SQL Server? I can separately query all the server or database level permissions, but I want to know other thing. What server role do the given permissions exactly belong to? For example I want to see all the permissions belonging to securityadmin server role, hence I'll be able to understand what privileges the role has. I want the same for database roles. Can I find this info in SQL Server system tables?

Thanks

Hope the following would help:

Server Level permissions

For server role permissions
sp_srvrolepermission
For Logins permissions

select  ge.name as Grantee,
        gr.name as Grantor,
        o.name as ObjectName,
        p.permission_name,
        P.state_desc
from sys.server_permissions as p
        left join sys.server_principals as Ge on p.grantee_principal_id = ge.principal_id
        left join sys.server_principals as Gr on p.grantor_principal_id = gr.principal_id
        left join sys.objects as o on p.major_id = o.object_id

Database Level Permissions

select  ge.name as Grantee,
        gr.name as Grantor,
        o.name as ObjectName,
        p.permission_name,
        state_desc
from sys.database_permissions as p
        left join sys.database_principals as Ge on p.grantee_principal_id = ge.principal_id
        left join sys.database_principals as Gr on p.grantor_principal_id = gr.principal_id
        left join sys.objects as o on p.major_id = o.object_id
go