Is there a policy or special server configuration to limit SQL Server query results? I have been seeing developers using SELECT * FROM table when testing their queries.

The issue is that many developers are executing such SELECT statements on huge tables, which slows down SQL Server performance.

We have told them to use SELECT TOP 100 * FROM table, but they don't do it.

Is there a policy or special configuration to make SQL Server show limited results (i.e. 100 rows) on huge tables? If so, which SQL Server version does that?

There is no native way to accomplish directly what you are looking for.  Why do developers have access to systems they can inherently affect the performance on where it matters?  Are these production systems?  If so, do these developers need access to prod?  If not (non-prod), then why do you care what developers do to development database servers?  Maybe I'm missing something.  Developers and non-operational professionals typically don't have access to environments where performance impact is considered.

Outside of the business process, a solution that you could use if you are unable to change access is Resource Governor to isolate resource usage.

Another thing you could do is create views where you embed your "top" logic, and give the developers access to the views but not the underlying tables.  That way they could do select * from viewname all day, but the underlying definition is whatever you want.


Does SentryOne Plan Explorer count the reads in UDFs? I have a query like this:

select dbo.fn_complexFunction(t.id)
from mytable t

In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan to make the Query Plan include the UDF.

When running 'Get Actual Plan', it doesn't appear that the logical reads and other metrics include the operations that occur in the UDF. In cases like this, is the only workaround to use Profiler?

The biggest problems we have here are:

Like @JNK says, SQL Server obfuscates away the use of a UDF, and does terrible things with them anyway (like always estimates one row). When you generate an actual plan in SSMS, you don't see its use at all either. Plan Explorer subject to the same limitations because it can only provide the information about a plan that SQL Server provides.
The code relies on different sources for runtime metrics when generating an actual plan. Unfortunately the plan XML does not include function calls, and SQL Server doesn't reveal the I/O incurred by a function when using SET STATISTICS IO ON; either (this is how the Table I/O tab gets populated).

Consider the following view and function against AdventureWorks2012. This is just a silly attempt at returning a random row from the detail table given a random row from the header table - mostly to make sure we generate as much I/O as possible, every time.

CREATE VIEW dbo.myview
WITH SCHEMABINDING
AS
  SELECT TOP (100000) rowguid, SalesOrderID, n = NEWID()
    FROM Sales.SalesOrderDetail ORDER BY NEWID();
GO

CREATE FUNCTION dbo.whatever(@SalesOrderID INT)
RETURNS UNIQUEIDENTIFIER
WITH SCHEMABINDING
AS
BEGIN
  RETURN
  (
    SELECT TOP (1) rowguid FROM dbo.myview
     WHERE SalesOrderID = @SalesOrderID ORDER BY n
  );
END
GO

What Management Studio Does (and Does Not) Tell You

Take the following query in SSMS:

SET STATISTICS IO ON;

  SELECT TOP (5) SalesOrderID, dbo.whatever(SalesOrderID)
    FROM Sales.SalesOrderHeader ORDER BY NEWID();

SET STATISTICS IO OFF;

When you estimate a plan, you get a plan for the query and a single plan for the function (not 5, as you might hope):

You don't get any I/O data at all, obviously, since the query wasn't actually executed. Now, generate an actual plan. You get the 5 rows you expected in the results grid, the following plan (which makes absolutely no visible mention of the UDF, except in the XML you can find it as part of the query text and as part of the Scalar Operator):

And the following STATISTICS IO output (which makes absolutely no mention of Sales.SalesOrderDetail, even though we know it had to read from that table):

  Table 'SalesOrderHeader'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What Plan Explorer Tells You

When PE generates an estimated plan for the same query, it knows about the same thing as SSMS. However it does show things in a slightly more intuitive way. For example, the estimated plan for the outer query shows how the output of the function is combined with the output of the query, and it is immediately clear - within a single plan diagram - that there is I/O from both tables:

It also shows the function's plan by itself, which I'm only including for completeness:

Now, let's take a look at an actual plan, which is thousands of times more useful. The downside here is, again, it only has the information that SQL Server decides to show, so it can only expose the graphical plan diagram(s) that SQL Server provides. This isn't a situation where someone decided not to show you something useful; it just doesn't know anything about it based on the plan XML that is provided. In this case, it is just like in SSMS, you can only see the outer query's plan, and it's as if the function isn't being called at all:

The Table I/O tab, too, still relies on the output of STATISTICS IO, which also ignores any of the activity performed in the function call:

However, PE gets the entire call stack for you. I have occasionally heard people ask, "Pffft, when will I ever need the call stack?" Well, you can actually break down the time spent, CPU used, and number of reads (and, for TVFs, number of rows produced) for every single function call:

Unfortunately, you don't have the ability to correlate that back to which table(s) the I/O is coming from (again, because SQL Server doesn't provide that information), and it isn't labeled with the UDF name (because it's captured as an ad hoc statement, not the function call itself). But what it does allow you to see, that Management Studio doesn't, is what a dog your UDF is being. You still have to join some dots, but there are fewer dots and they're closer together.

About Profiler

Finally, I would strongly recommend staying away from Profiler, unless it is to set up a server-side trace that you are going to script and then run outside the scope of any UI tool. Using Profiler against a production system is almost certainly going to cause more problems than it will ever solve. If you want to get this information, please use a server-side trace or extended events, and be sure to filter very wisely. Even without profiler, a trace can impact your server, and retrieving showplans through extended events is not the most efficient thing in the world either.


Do schema changes "break" Availability Groups or are they handled transparently? My organization is planning to adopt SQL Server 2012 Availability Groups and I am trying to understand what impact (if any) it will have on our application upgrade process.

We release application updates on an 8 week cycle and any release could include schema changes and/or data migrations.

What I am trying to understand is whether or not the HA/DR solution handles the schema changes transparently (new columns, indexes get added to secondaries) or is manual intervention required to create the schema on each instance and then turn Always On back on.

The data migration piece I'm assuming is handled transparently but would like to confirm that as well.

I guess I am also making a blanket assumption that there is no difference in these behaviors based on the Availability Groups configuration which may be false as well.  Please let me know.

In a nutshell; In any given release of my application I may change a very large table (10s to 100s of millions of records) by adding columns to it.  Some columns may be "net new" so they can make use of the Enterprise Online schema change functionality.  Other columns may be a refactoring of an existing column (FullName gets split into FirstName and LastName) and a migration will be run for each row in the table to populate these fields. Do any of these behaviors require DBAs to change the AlwaysOn configuration or is this handled by default and all secondaries get the DDL and DML statements "for free"?

Thanks for any clarity you can provide.

Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that happens in Vegas has to stay in Vegas. :-)

Where you may want to be careful, is when you have an application that points to the primary, and you update that to match schema changes. But you may have a different application that points to the secondary (e.g. with read-only intent), and that app change would have to be synchronized as well.

Another potential gotcha is when your database that is part of an availability group has references to objects in other databases (e.g. a static lookup table that is stored in a utility database). If those change and the AG depends on those objects, you will have to push those changes manually. The same is true for jobs, server-level logins, linked servers etc. - anything that lives outside of the database and/or is not transactionable. Database users can get orphaned (contained users aside). I know this is probably obvious but wanted to list it explicitly for completeness.


Postgres: Are secondary indexes included in ACID? Are non-unique indexes / indices covered under the Consistency clause in aCid? (same for other attributes of an index that do not place constraints on the data) I am seeing certain performance issues (benefits, actually) in Postgres that make me wonder if they are.

Given that indexes / indices are not first-class objects (i.e. you can't access them directly in Postgres, nor can you request their use), I see no reason at all why Postgres would be REQUIRED to support this. I can find no definition of ACID that says "indexes have to absolutely complete and not be hacked up before the transaction can finish".

Under certain conditions that do not place restrictions on the insert (such as the index not being unique), the index could essentially be "invalidated" (i.e. "don't use it until I'm finished reindexing"), or flags can be set that says "the index does not cover the following ranges".

If Postgres played this trick, the copy from operator could be made exceedingly swift (which is what I am seeing), similarly for massive insert counts in a transaction.

I'm not just making this up...

While Redshift is a bad example, Amazon weasels out of Consistency by playing tricks with how it stores the (one and only) sort key (essentially a primary index-ish construct in Redshift). Until one performs a vacuum command, the primary key just keeps getting worse and worse and your database starts becoming a black hole: queries go in, but no results come out.

Clearly, an internalized vacuum regimen would prevent the Redshift silliness that often occurs during mass imports.

Are non-unique indexes / indices covered under the Consistency clause in aCid?

Yes. Any violation of that would be considered a bug in PostgreSQL.

The docs you quoted are cases where postgres might have to temporarily scan the heap instead of doing an index-only scan or otherwise do extra work to get a consistent result.

For example, both BRIN and GIN indexes accumulate batches of pending changes and then do batch updates. When the indexes are used in queries this queue is also scanned to make sure that a current, up-to-date and consistent view is seen.

If an index is currently invalid it'll be skipped by the planner and won't be used by queries.

Redshift isn't really PostgreSQL, it just happens to share the same front-end and protocol. Drawing comparisons based on Redshift will typically just create confusion.


"select count(1)" performance in a CTE Given the below CTE:

with seekResults as
(
select DISTINCT
 trx_id_fk,
 account_FK,
 customer_cif,
 customer_tin,
 TIN_Type,
 ID_NUMBER ,
 SEARCH_NUMBER
 FROM transactionsOP
)

 select trx_id_fk
  ,case when account_FK is not null then (select count(1) from seekResults a where a.account_fk = s.account_fk and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   when customer_cif is not null then (select count(1) from seekResults a where a.customer_cif = s.customer_cif and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   when customer_tin is not null then (select count(1) from seekResults a where a.customer_tin = s.customer_tin and a.TIN_Type = s.TIN_Type and a.SEARCH_NUM = s.SEARCH_NUM)
   when ID_NUMBER is not null then (select count(1) from seekResults a where a.ID_NUMBER = s.ID_NUMBER and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   else (select count(1) from seekResults a where a.SEARCH_NUMBER= s.SEARCH_NUMBER)
       end as 'transaction_period'
from seekResults s  ;

I noticed that it is taking a lot of time doing the calculation because it is calling the seekResults CTE over and over again, mostly in the select count(1) section:

select count(1) from seekResults  a

Is there a better way to improve that part ? So far I haven't found anything to replace it with.

I think your query can be written using window aggregates. I don't know if it will be more efficient though:

with seekResults as
(
  select distinct
    trx_id_fk,
    account_fk
    customer_cif,
    customer_tin,
    tin_type,
    id_number,
    search_number
  from transactionsOP
)
select
  trx_id_fk,
  case
    when account_fk is not null then
      count(*) over (partition by search_number, account_fk)
    when customer_cif is not null then
      count(*) over (partition by search_number, customer_cif)
    when customer_tin is not null then
      count(*) over (partition by search_number, customer_tin, tin_type)
    when id_number is not null then
      count(*) over (partition by search_number, id_number)
    else
      count(*) over (partition by search_number)
  end as transaction_period
from seekResults ;


Can't get data from stored procedure After working on my database a bit, I am working on a VB.Net app that accesses it using LINQ.  I have the following stored procedure in the database:

CREATE PROCEDURE dbo.GetTableColumn(
    @ColName VARCHAR(MAX),
    @TblName VARCHAR(MAX),
    @Result  BIT OUT
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName))
        BEGIN
            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 'SELECT ' + @ColName + ' FROM ' + @TblName
            EXEC (@SQL)
            SET @Result = 1
        END
    ELSE
        SET @RESULT = 0
    RETURN @Result
END

I have added it to my DataLINQContext and call it in the following method in my VB.Net code:

Public Function GetTableColumn(ByVal col As String, ByVal table As String) As AutoCompleteStringCollection
    GetTableColumn = New AutoCompleteStringCollection
    Using dbContext As New Customer_LINQDataContext
        Dim result As Boolean
        Dim query = dbContext.GetTableColumn(col, table, result)
        MessageBox.Show(query.ToString())
    End Using
End Function

Now what I am trying to accomplish, is that the query object receives the data from the query performed by the procedure.  This does not happen.  Instead I only get a 1 or 0 result, based off whether or not the column exists within the table.

So obviously something is wrong somewhere.  I feel it's a mess up on the part of my procedure, but I am not sure how to code it to accomplish what I want it to do.

Your code is obtaining the result of the RETURN @result at the end of the stored proc.  The results you want are being passed out of the stored procedure  as a ResultSet.  You need to use a DataReader object to see the rows.

I'm not sure how to do that using Linq.  You'll hopefully get a better answer on your StackOverflow question.

Sample code for a VB.Net console application to read the results of executing a Stored Procedure:

Module Module1

    Sub Main()
        Dim cConnectionBuilder As New SqlClient.SqlConnectionStringBuilder
        Dim sConnectionString As String

        cConnectionBuilder.ApplicationName = "Test App"
        cConnectionBuilder.ConnectTimeout = 15
        cConnectionBuilder.DataSource = "<servername here>"
        cConnectionBuilder.InitialCatalog = "tempdb"
        cConnectionBuilder.IntegratedSecurity = True
        cConnectionBuilder.Pooling = False
        sConnectionString = cConnectionBuilder.ConnectionString

        Using cConnection As New SqlClient.SqlConnection(sConnectionString)
            cConnection.Open()
            'drop the existing sample_proc procedure if it exists
            Using cCommand As New SqlClient.SqlCommand("IF OBJECT_ID(N'dbo.sample_proc', N'P') IS NOT NULL DROP PROCEDURE dbo.sample_proc;", cConnection)
                cCommand.CommandType = CommandType.Text
                cCommand.ExecuteNonQuery()
            End Using
            'create the sample_proc procedure
            Using cCommand As New SqlClient.SqlCommand("CREATE PROCEDURE dbo.sample_proc (@TestID int) AS BEGIN SELECT name FROM sys.columns; RETURN 1; END", cConnection)
                cCommand.CommandType = CommandType.Text
                cCommand.ExecuteNonQuery()
            End Using
            'execute the sample_proc procedure, and print a line in the console window for each row in the ResultSet
            Using cCommand As New SqlClient.SqlCommand("dbo.sample_proc", cConnection)
                cCommand.Parameters.Add(New SqlClient.SqlParameter("@TestID", 1))
                cCommand.CommandType = CommandType.StoredProcedure
                Using dReader As SqlClient.SqlDataReader = cCommand.ExecuteReader
                    While dReader.Read
                        'get each row from the resultset
                        Dim sName As String = dReader.GetString(dReader.GetOrdinal("name"))
                        Console.WriteLine(sName)
                    End While
                    dReader.Close()
                End Using
            End Using
            cConnection.Close()
        End Using
        Console.WriteLine("Press 'Q' key to quit.")
        Dim c As ConsoleKeyInfo = Console.ReadKey
        Do Until c.Key = ConsoleKey.Q
            System.Threading.Thread.Sleep(1)
            c = Console.ReadKey
        Loop
    End Sub

End Module

Output looks like this:

account_name
account_name
addr
Press 'Q' key to quit.


How should I use RMAN clone refresh using set until time? I am using this technique to refresh dev. environments with production data.

The business process is to request a tape hold for the full backup I want to use for a clone refresh to dev. Once this is imported, I use the exact server end time of the database backup (not the logs backup) for my set until time clause.

run {
set until time = "TO_DATE('08/09/2014 20:24:47','MM/DD/YYYY HH24:MI:SS')" ;
allocate auxiliary channel t1 type 'sbt_tape';
duplicate target database to DEVDB;
}

This works only some of the time. More often than not, the script goes looking for a previous backup which is not in tape hold.

  Media required
  Detected Criteria: Media Handling Required
  Job ID:
  Status: Media is not in Library
  Library Name: SEA-TAPELIB07 (i500 B)
  Media label: SVL432L4
  Failure Reason: The required media [SVL432L4] for the job [2518965] is not in the Library [TAPELIB (9999)]. Please use the import functionality to import the media from the location [Not Available] into the library [TAPELIB (9999)].
  Media List: Not Applicable

As a workaround, I requested the previous backup and re-ran the script. It completed but it used the previous day's backup and the request was fulfilled two days late. What am I doing wrong with this technique or should I be using a different method? Any suggestions would be helpful.

First you need to ensure that that the backup is in your library (surely you know better ways for find it, I put my query):

select
    D.DB_NAME, B.DB_ID, BASET.BCK_TYPE,
decode(BASET.CONTROLFILE_INCLUDED,'BACKUP','YES','NO') as CONTROLFILE_INCLUDED,
BASET.INCR_LEVEL, bapiece.tag, bapiece.handle, bapiece.start_time,
bapiece.completion_time,
round(bapiece.bytes/1024/1024,2) as Mb
from rman.bp bapiece,
        rman.dbinc d,
        rman.bs baset,
        rman.db b
where BAPIECE.DB_KEY=D.DB_KEY
   and D.DB_NAME='SFPROD'
   and BASET.DB_KEY=D.DB_KEY
   and BASET.BS_KEY = BAPIECE.BS_KEY
   and  bapiece.start_time>sysdate-1
   and B.DB_KEY=D.DB_KEY
---   and bapiece.handle like '%.dbf%'
order by tag desc, handle desc    ;

And... I hate to use "set until time" to restore/duplicate database. It is more human friendly but you can be surprised with an rman error at the end of the restore. Try to find the last commit (SCN) for duplicate process:

SQL> select max(next_change#)
    from v$archived_log
    where archived = 'YES'
    group by thread#;

or with rman:

RMAN> list backup of archivelog all;
--Next SCN

And then:

run { set until scn ... ; allocate auxiliary channel t1 type 'sbt_tape'; duplicate target database to DEVDB; }


What is the correct way of creating a one-to-one relationship? Imagine the following situation:

> A person has a passport.
> A person *owns only* one passport.
> One passport can only *be owned by a single* person.

This is a clear case of a one-to-one relationship. For the sake of simplicity let us imagine that a person only has a name and the passport only has a nationality. What is bothering me the most is that it seems that everyone is doing this differently. From what I can tell there are four strategies that can be taken to map this relationship:

Everything on the same table:

A foreign key on the owner side that references the owned side.

Same value for primary keys on both tables.

A foreign key on the owned side with a unique key on top:

Number 1 is pretty self explanatory and I do not see any problem when
the entities are small, but if they have a lot of fields we will have
a gigantic table.
Number 2 seems fine and this is the way I have seen most people and frameworks do it  (e.g. Entity Framework, Hibernate) and it is also the way I was taught in school. The big problem here is that, because of referential integrity, our deletion logic will be upside down. I should be able to delete a passport without any problem, but in this case I will not be able to delete it without deleting the person as well, which does not make any sense.
Number 3 and 4 seem pretty much identical. With the foreign key on the passport side, I am able to delete passports without deleting people, which makes sense. If I remove a person, I should have to remove their passport. The main advantage that I see for using option number 4 instead of number 3 or all the others, is that if for ever reason I decide that I now want a user to have multiple passports I only have to remove the unique key constraint, which is incredibly easy and a lot less hassle than changing the keys from both tables.

So my question basically is reduced to the following: With so many advantages with number 4 why do people keep using the other strategies? My biggest grip is with ORMs such as Hibernate which, in my opinion, do things the opposite way. The owning side of the relation tracked by Hibernate is the side of the relation that owns the foreign key in the database. So if I tried to do this in Java with Hibernate my relationship would be swapped. If I included the foreign key to the passport on the person table like it wants me too, it would ruin the deletion logic like I have explained before. I have the impression that EF also works this way. So, with all of this, why do people keep preferring the "disadvantageous" approaches?

The most important thing is to word constraints precisely and not allow for logical errors. Options 1, 2, 3 contain logical errors. Option 4 is close, but may not represent reality very good -- in general one person may have more than one passport (dual citizenship).

Option 1 is not realistic, because it states that there can not exist a person without a passport.

Option 2 essentially allows a passport to exist without a person..

Option 3 states that passport is a person.

Option 4 is the closest and can be worded as:

[P1] Person (PERSON_ID) named (NAME) exists.

(c1.1) Person is identified by PERSON_ID.

(c1.2) Each person has exactly one name; for each name, more than one person can have that name.

[P2] Passport (PASSPORT_ID) issued by country (COUNTRY) is owned by person (PERSON_ID)

(c2.1) Passport is Identified by PASSPORT_ID.

(c2.2) Each passport is issued by exactly one country; for each country, more than one passport can be issued by that country.

(c2.3) Each passport is owned by exactly one person; for each person that person may own at most one passport.

(c2.4) If a passport issued by a country is owned by a person then that person must exist.

person {PERSON_ID, NAME}  -- p1
   KEY {PERSON_ID}        -- c1.1

passport {PASSPORT_ID, COUNTRY, PERSON_ID}  -- p2
     KEY {PASSPORT_ID}                      -- c2.1
     KEY {PERSON_ID}                        -- c2.3

FOREIGN KEY {PERSON_ID} REFERENCES person {PERSON_ID} -- c2.4

Note:

[Px]   = predicate x
[cx.y] = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

All attributes NOT NULL

EDIT

Just to be clear, option 3 would have been the correct choice had you chosen a different example. Say, Person & Employee or Employee & Accountant instead of Person & Passport. Because an employee is a person, and an accountant is an employee. The is-a relationship implies a proper subset.

This is a great example of how focusing on technical details may introduce logical errors, and there is no such a thing as a small logical error.


Backup Oracle DB using TSM server version 6.3.0 I'm new to Oracle and TSM server. I installed Oracle11gR2 and TSM server version 6.3.0 (linux Centos 6.4 OS).
I did the configuration and everything works fine. so far I arrive to run the following script using rman:

run {
allocate channel Channel1 type 'SBT_TAPE' parms
'ENV=(DSMO_NODE=TDPOTSMUSR,DSMO_AVG_SIZE=1,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/
tdpo.opt,DSMI_LOG=/opt/tivoli/tsm/client/api/bin64/logs)';
backup
        full
        format 'b_%u_%p_%c'
        database;
release channel Channel1 ;
}

I need to know how to backup theOracle DB to a storagepool name ORADisk? I need to understand a bit what does channel mean in a script and what does it actually do.
really appreciate your hel and reply. thank you

to backup the Oracle DB to a storagepool name ORADisk this is done through backup policy by tsm server not the client by assign the client to a domain then assign it to the group policy.

channel is oracle rman command used to hold the new settings for this backup session only without any changes to the rman predefined settings.


Null Or Multiple Parameters When Querying I have a stored procedure that has a parameter that must take a comma delimited varchar list of values that can be turned into a table of values or can be NULL, such as

@Values VARCHAR(10) = NULL

OR

@Values VARCHAR(10) = '1,5,10'

IF OBJECT_ID('TEMPDB..#Parameters') IS NOT NULL DROP TABLE #Parameters
CREATE TABLE #Parameters ( Id INT )

INSERT INTO #Parameters
SELECT
    [Value]
FROM
    FunctionThatReturnsStringToTable(',',@Values)

I need to figure out a way to make this work in SQL like so

SELECT
   SomeColumns
FROM
   TABLE
WHERE
   COLUMN = IIF(@Values IS NULL, COLUMN, (SELECT Id FROM #Parameters)

Which I know will not work. Is there any other way around this?

You can do it this way (notice this avoids the unnecessary #temp table):

SELECT SomeColumns
FROM dbo.TABLE AS t
LEFT OUTER JOIN dbo.FunctionName(',',@Values) AS p
ON t.COLUMN = p.Value
WHERE t.COLUMN = CASE WHEN @Values IS NULL THEN t.COLUMN ELSE p.Value END;

But much, much, much better to pass @Values as a TVP instead of a string you have to split.

No idea what your split function actually looks like, but have a look at these posts:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql


SQL Server 2008 R2 "Ghost Memory"? We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit Windows Server 2008 R2 Enterprise (it is a Dell PowerEdge 2950).

The strange problem is that the system is reporting 82% of memory in use but sqlservr.exe is only reporting 155mb in use. The reason that I suspect SQL Server is the issue is because if I restart the sqlservr.exe process the memory consumption returns to normal for a period of time.

Does anyone have any ideas on how I can start to track this issue down?

Thanks, Jason

You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at:

SQLServer:Memory Manager\Total Server Memory perfmon counter
DMVs

I can't recall if there is a DMV or combination of that will give you the total memory allocation but the following will show the bulk of it.

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second is the most interesting usually, buffer pool allocations by database. This is where the lions share will be used and it can be useful to understand which of your databases are the biggest consumers.


Is there a way to allow a `unique` key to be the same on a max of 3 records? Is there a way to allow a unique key to be the same on a max of 3 records?

I have a table with urls in it. I don't want my program to put the same url in more than three records. It has a reason for three, but any more is too much. Any idea how I can make that happen?

Are the URLs coming from a normalized URL table and hence you just have UrlIDs in this table, or is this the source table for URLs? The distinction here is important because you are implying that, in the case of this table being the source of the URLs (which it sounds like it is), you are planning on creating a UNIQUE Constraint or Index on the URL field. Even if they did allow for specifying the number of allowed entries per distinct value, this plan still wouldn't work due to indexes only allowing up to 900 bytes. A lot of URLs are way over that limit, and considering that you should be using NVARCHAR to store the URLs (since they can have Unicode characters in them, even if that is somewhat rare, hence you should also enable DATA_COMPRESSION if you are using Enterprise Edition and SQL Server 2008 or newer) that is only 450 (generally speaking) characters, which is even more limiting.

You should create an AFTER INSERT, UPDATE trigger to enforce this rule. Using a trigger will allow you to enforce this rule regardless of the source of the INSERT or UPDATE, whereas putting this logic into stored procedures is not guaranteed to catch all attempts to insert or update the table, especially ad hoc queries.

In terms of performance, depending on how many rows this table will have over time, it certainly will slow things down a little to have to do this check each time. You should find ways to narrow down a subset of the data for faster searching so that you don't hold up all INSERT and UPDATE queries. Some things to consider:

Normalize the URLs out to another table. URLs can be up to 2048 characters (I believe) so if you know that you will generally have repeats, create a separate URL table that will have the unique URLs and just place a UrlID in this table. Then you can easily index the UrlID Foreign Key field. This approach will save tons of disk space, make the trigger much faster, and actually make most queries (especially those that don't need the URL text) against this table faster as you will be fitting many more rows on each data page.
If you need to keep the URL in this table, add a UrlHash VARBINARY field and populate that with the result of HASHBYTES. The UrlHash field can also be indexed in which case you could consider adding the Url field to that index as an INCLUDE column (which doesn't have the 900 byte limit, but also is not sorted and has no statistics). Then your trigger can search on the HASHBYTES value of the incoming value and any entries that match would then compare the full URL text. Yes, you need to compare both in the case where the hash value matches since there can be collisions on the hash values across different source strings. But it would help you easily eliminate non-matches which should be the vast majority of the time.

This UrlHash field should be a non-Persisted Computed Column (and indexed as noted above). This will ensure that the hash value is always in sync with the source string. The reason for "non"-Persisted is that being PERSISTED is not required for that value to be in the index, and the value is only needed for the index, so there is no reason to take up the extra space. Please see the MSDN documentation for Indexes on Computed Columns for more details, if interested.


OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql) I would intuit that it's fine, but I just want to make sure there are
no gotchas from a recovery point of view:

If I were to lose my temp tablespace upon system crash, would this
prevent proper crash recovery?

Also, if I were to omit the temp tablespace from the base backup,
would that prevent proper backup recovery?

If you are referring to the base/pgsql_tmp, then you should be fine. But I don't speak from experience of having done that myself. The only gotcha is that you have to make sure that the location is accessible when your PostgreSQL server starts up.

(Ref.: Book PostgreSQL 9.0 High Performance, page 93).

The book in question refers to creating a simlink of the base/pgsql_tmp on the OS partition or other less "safe" partition (i.e. non-RAID or the like).


What data will be lost with DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)? I have a corrupt database.  The last known good backup was over 2 weeks ago.  I've run a CHECKDB and have the page numbers where SQL Server located the corruption.  I've looked at the data on the pages before and after the corrupt pages using DBCC PAGE and I am certain that the corrupt data is contained to a single day back in 2013.  The last known good backup, only being 2 weeks old, will have that day from 2013 on clean pages.

My plan is this:

Restore last known good backup to a new database.
Run DBCC CHECKDB (corrupt_database, REPAIR_ALLOW_DATA_LOSS) on the corrupt database and let it correct the issue.
Insert the deleted records back into the old database from the new database.

I'm afraid of running REPAIR_ALLOW_DATA_LOSS because everything I've read online says it should be used as a last resort.  I'm not sure exactly what it will affect.  If it deletes pages, will it only touch the pages that CHECKDB found as corrupt?

But...If REPAIR_ALLOW_DATA_LOSS removes those corrupt pages, but I know what data I will lose, can I not just restore the backup to a new database and insert the data that was deleted in the old database?  I hope I'm making sense.

Please let me know your thoughts.

Yes, if you know what data to insert after corrupt data pages are deleted, you should be fine.

make sure you find out the root of corruption to prevent it from happening.

also, once everything is fixed, then do a full back and restore it on a different server to make sure everything is fine.

Your plan sounds good to me.


How can I get all rows of data not included in a TVP I am trying to do the following:

declare @domains DomainNames;
declare @CompanyId int
declare @CompanyName varchar(255);
declare @deletedList DomainNames;

set @CompanyId = 239;
set @CompanyName = 'dave';
insert into @domains (Domain) values ('ev2.com'), ('ev3.com');

select * from CompanyDomains where CompanyId = @CompanyId and EmailDomain != @domains

where I get back all rows in the table that do not have an EmailDomain value matching any of the values in @domains (and the CompanyId has a set value). Is there a way to do this?

Sql Server 2008 & Sql Azure.

thanks - dave

Using NOT EXISTS should work, eg

SELECT *    --!!TODO add column list
FROM dbo.CompanyDomains cd
WHERE CompanyId = @CompanyId
AND NOT EXISTS (
    SELECT *
    FROM @domains t
    WHERE cd.Domain = t.domain
    )


Guidance on simplifying the following diagram I have a case scenario where a customer can book accommodation or flight only or both.

I have modeled the accommodation part, however i am making things complex by over-thinking how stuff works in real life.

What i have thought is that a customer cannot book a flight because it is referring to a whole aircraft, very unlikely. Therefore i thought that a customer books a flight seat.

The following is what i have done so far, but i have made it too complex.

Can someone guide me to make this simpler? Could i merge flight_seat with flight and aircraft? please help

No, you shouldn't merge the 3 tables.

For a given flight, aircraft may change
For a given aircraft, flight may change

However, I'd suggest that your model isn't complex enough

FlightSeats depends on Aircraft model (seat layout etc)
Aircraft departs Airport too...
.. but Flights leave and departs Airports. Or is it FlightAirport?
Flight is arguably 2 entities: the base flight (number, schedule etc) and the actual FlightInstance (date etc)

Have you written down all your facts first? Say in tabular format like this MSDN example (actually for ORM which I use because ERDs don't capture all the information you need)

Edit, more...

As per @ypercube and @Simon Righart comments below
From @Simon Righart: A given aircraft might have three different classes of seats (economy, business + first) with different numbers of each each flight number might be flown by multiple aircraft


SSIS Control Flow: Precedence constraints not working as expected The control flow of my SSIS package is not behaving as expected.

What I want to achieve:

Test if CLR is enabled on SQL Server using

SELECT value
FROM   sys.configurations
WHERE  name = 'clr enabled'

If value == 0, attempt to enable CLR and display message Server is configured.
If value == 1, display message Server is configured.

If I setup my control flow to do only one or the other, it works.

This flow works as expected. However, I would like to, if value == 0, continue to the MessageBox after Enable CLR on Server.

I tried changing the control flow to this

Now, what happens is this:

value == 0 [CLR Enabled on Server] hands control to [Enable CLR on Server], which completes and exits.
value == 1 [CLR Enabled on Server] completes and exits.

[MessageBox- Server is Configured] is never reached.

Can anyone help me understand this, and/or point me to a good resource about SSIS Conditional Control Flow?

Your precedent constraint defaults to an AND situation. The preceding task must return success and the expression must be true.

In binary scenarios like this, only one of those two tasks is ever going to happen. By adding the next level of constraint in - both tied to the message box step, the final step is waiting for the "Enable CLR on server" to be successful as well as "CLR Enabled on Server" to be successful and for the value to be equal to 0.

Since only one of those situations will work out, you need to swing the PC to be OR situation

That results in the green dotted lines instead of solid

As an FYI, in your Script task, assuming this runs in an automated fashion (SQL Agent), since you're using a message box, it will fail. There's a System level variable, System::InteractiveMode which is a boolean. It indicates whether you're running in an environment where you can interact with the desktop or not. If that evaluates to false and you attempt any GUI activity, the package will error out.

I find it helpful to add code like the following

public void Main()
{
    // TODO: Add your code here
    bool interactiveMode = (bool) this.Dts.Variables["System::InteractiveMode"].Value;
    bool fireAgain = false;
    string message = "This is some message I likely built out using other SSIS Variable values";

    if (interactiveMode)
    {
        MessageBox.Show(message);
    }

    this.Dts.Events.FireInformation(0, "CLR Check", message, string.Empty, 0, ref fireAgain);

    Dts.TaskResult = (int)ScriptResults.Success;
}

There I have a check against my interactive mode and only attempt to display something if I can. I also go ahead and fire an OnInformation event because that's automatically logged in the 2012+ Project Deployment Model, shows in both the GUI and the Output panel in Visual Studio/SSDT/BIDS and is easily recorded from DTEXEC.exe call by appending I to the reporting parameter i.e. /rep EI


How to change ibdata1 path in mysql? My ibdata1 file is keep on increasing in size and for a temporary solution, we want to move the file to a separate location with more hard disk space.

But, when I change the my.cnf file parameters data_dir and innodb_data_home_dir and restart the service, it is not starting and throwing errors like

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
ERROR! The server quit without updating PID file

Please help. I need it immediately as it is the produciton database and the space is almost full.

Here is the configuration file for your reference:

[mysqld]
port=3316
datadir=/data/databases/mysql
socket=/var/lib/mysql/mysql.sock
user=root
slow_query_log = 0
long_query_time = 10
log-output = FILE
sql_mode = STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_UNSIGNED_SUBTRACTION
max_sp_recursion_depth = 40
group_concat_max_len = 30000
max_tmp_tables = 64
key_buffer_size = 64M
max_allowed_packet = 16M
table_open_cache = 256
sort_buffer_size = 8M
read_buffer_size = 8M
join_buffer_size = 8M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size = 16M
binlog_cache_size = 8M
thread_stack = 384K
thread_concurrency = 23
innodb_thread_concurrency = 46
innodb_data_home_dir = /data/databases/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/databases/mysql
innodb_buffer_pool_size = 30G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 600
innodb_rollback_on_timeout = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysqlhotcopy]
interactive-timeout

MySQL version is 5.5.20

I've just done what you want! You'll have to move the entire datadir and not just a single file - not sure if that's possible.

For example, in my datadir, you will find:

pol@pol:~/Downloads/sware/mysql/mysql-5.5.58-linux-glibc2.12-x86_64/data$ ls -lista
total 28724
32774484  5120 -rw-rw----  1 pol pol   5242880 Oct 24 12:47 ib_logfile0
32774483 18432 -rw-rw----  1 pol pol  18874368 Oct 24 12:47 ibdata1
32774487    16 -rw-rw----  1 pol pol     13498 Oct 24 12:47 mysql-bin.000001
32774488     4 drwx------  2 pol pol      4096 Oct 24 12:47 ptest
32774151     4 drwxrwxr-x  6 pol pol      4096 Oct 21 11:55 .
32774465     4 -rw-r-----  1 pol pol      2219 Oct 21 11:46 pol.err
32774486     4 -rw-rw----  1 pol pol         6 Oct 21 11:46 pol.pid
32774482     4 -rw-rw----  1 pol pol        19 Oct 21 11:46 mysql-bin.index
32774485  5120 -rw-rw----  1 pol pol   5242880 Oct 21 11:46 ib_logfile1
32643837     4 drwxrwxr-x 13 pol pol      4096 Oct 21 11:40 ..
32774334     4 drwx------  2 pol root     4096 Oct 21 11:34 mysql
32774404     4 drwx------  2 pol pol      4096 Oct 21 11:34 performance_schema
32774152     4 drwxrwxr-x  2 pol pol      4096 Oct 21 11:26 test

Files like ibdata1 and ib_logfile are very important! So, it's important to copy the whole datadir!

Step 0 - backup everything - DBA's motto - "Backup or F***up!" :-)

Also, you say that this is a production server - test on a non-prod
machine first.

First, take a copy of your datadir - mine's called simply data. As a dba, you should always have a path for backing out your changes!

cp -r ./data ./databak

Then copy your old data to your new directory - with your mysqld server stopped, otherwise you will have inconsistencies.

cp -r data datatest

Then remove your old data directory

rm -rf data

Edit your my.cnf file replacing your old datadir location with your new one, or adding it, if you were using a default.

vi my.cnf

Then kick off your server again.

Et voilà!


How do you make manual/one-off log-based queries? My app engine instances are logging incoming requests and I'd like to be able to run manual/one-off queries for data analysis.

As an example, I would like to be able to determine how many requests are made to each end-point, within a given period.
So an SQL query might look something like this:

SELECT path, count(path)
FROM request_log
WHERE time >= :start_time AND time <= :end_time
GROUP BY path

What is the simplest and most cost effective way to achieve these kinds of queries based on logging data?

Note: Monthly log ingestion is just under 1TB.

In GCP it'll be a two step process:

You should be able to create Log Filters:

https://cloud.google.com/logging/docs/view/advanced-filters

And then use those log filters to create metrics:

https://cloud.google.com/logging/docs/logs-based-metrics/counter-metrics

This won't be as flexible or seamless as a SQL query, but the filters should provide you with a way to select the logs you're interested in, and the metrics should allow you to count them.

If you are keen on using SQL, you will need to export your log data to Big Query.  You can do this manually when you need to, or you can set up sinks to automatically send new logs that match criteria to Big Query.  Once the data is exported, you would be using the Big Query service to run SQL on the logs.

Here is a link on exporting log data to Big Query:

https://cloud.google.com/logging/docs/export/


ORA--07445 error during online Re-org process in 10.2.0.4 database I was doing re-org of fragmented tables in an Oracle 10.2.0.4 production database:

sys@CLARITY> ALTER TABLE SOP_QUEUE ENABLE ROW MOVEMENT;
Table altered

sys@CLARITY> ALTER TABLE SOP_QUEUE SHRINK SPACE CASCADE;
ERROR at line 20:
ORA-03113: end-of-file on communication channel

When I checked the alert log, I found the following error:

ORA-07445: exception encountered: core dump [ktsk_get_prevl2()+217]
[SIGSEGV] [Address not mapped to object] [0x2E97317530] [] []

What could be the reason for this error, as I am unable to find a proper solution?

Bug 6494048 - Dump [ktsk_get_prevl2] from ALTER INDEX .. SHRINK SPACE (Doc ID 6494048.8)

This issue is fixed in

11.2.0.1 (Base Release)
10.2.0.5 (Server Patch Set)

Upgrade from that ancient, unsupported version.


What PL/pgSQL debugger can I use? I'm building a lot of PL/pgSQL functions and a debugger would really come in handy because RAISE NOTICE is not enough.

I read about a pgAdmin plugin you can install but it was only on Windows and I'm running Mac OS X.

Here's an article I wrote for how to get one installed on Ubuntu.

Installation instructions for the pldebugger for PostgreSQL

pg_config --version
"9.3beta2"

Look here for source code matching your version of PostgreSQL:
ftp://ftp.postgresql.org/pub/source/

For me, it was:

wget ftp://ftp.postgresql.org/pub/source/v9.3beta2/postgresql-9.3beta2.tar.bz2
tar xvjf postgresql-9.3beta2.tar.bz2
cd postgresql
USE_PGXS=1 ./configure
USE_PGXS=1 make
cd contrib
git clone git://git.postgresql.org/git/pldebugger.git
make
cd pldebugger
USE_PGXS=1 make
sudo USE_PGXS=1 make install

These instructions assume that you have the development packages for your version of PostgreSQL already installed on your system.


Entity Framework Core for SQL Server Failover Basic Availability Groups does not work I have set up an SQL Server Standard 2019 with Basic Availability Groups on a Windows Failover cluster. It is not like the Always On AG, but more of a limited version of it where DB operations are on the primary replica and the secondary assumes the primary role only if there is a failover.
I have a .NET Core 2.1 application with two instances that runs in docker Windows containers on Windows Server 2019, on different VMs with SQL. Since our host does not have nested virtualization enabled, we are stuck with Windows containers. They connect to the SQL using the following connection string:
Server=TCP:172.18.18.11; Database=db; User Id=****; Password=****;
Connection Timeout=120;MultipleActiveResultSets=True;
Failover Partner=TCP:192.168.11.24

There are some weak points though. Since we had a small number of VMs and we use containers mostly, we did not care much for setting up the Active Directory due to overhead and some bad previous experience with Windows Container Networking (especially DNS issues) when using AD.
We have successfully created the Failover Cluster but they are on different subnets and different physical locations 172.18.18.0/24 and 192.168.11.0/24. This caused some problems with allocating the cluster IP resource, and since we do not have an internal DNS, we are stuck with IP addresses. And we cannot use cluster domain name or use the listener IP address since instances are on different subnets as well. This is a problem with our host and we could not really resolve.
So the problem is, SQL Failover works, but client applications do not get aware of the change, despite the "Failover Partner" in the connection string. In case of a failover, our app responds with a SQL Exception:

The target database, 'db', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. Cannot continue the execution because the session is in the kill state. A severe error occurred on the current command. The results, if any, should be discarded.

Instead I would expect it to direct the query to Failover Partner. I did some research and apparently specifying the failover partner in connection string works (not sure if it works for BAG or Always On though).

I tried this connection string:
Server=TCP:172.18.18.19; Database=db; User Id=****; Password=****;
Connection Timeout=120;MultipleActiveResultSets=True;
Failover Partner=TCP:192.168.11.31

The IPs here are the listener IP addresses, I get this message:

Server TCP:192.168.11.31, database GWP2 is not configured for database mirroring.

So it looks like Failover Partner keyword works as it connects to 192.168.11.31. However it looks like it requires mirroring, maybe Always On Availability groups rather than BAG?

Ok, so I had to go out of my way and took a risk to setup DNS without AD. It wasn't a big deal since we had only 2 SQL instances and 3 client VMs. Apparently "Failover Partner" keyword only works with the deprecated "mirroring" feature. And the listeners are to be only accessed with their names. I don't think there is a solution to not using DNS without a number of ugly hacks.


How to optimize min/max queries for large tables on PostgreSQL How do you index a table in PostgreSQL so that min/max queries return as quickly as possible?

I have a large table with a couple hundred million rows. Each row as a source_id and a date when the record was last updated. I'd like to collect some statistics for each source_id, specifically the min and max date range for each source_id.

So I created this index on my table:

 CREATE INDEX CONCURRENTLY mydata_source_last_updated_date ON mydata (source_id, last_updated_date ASC);

However, when I try to query the min dates per source with:

SELECT source_id, MIN(last_updated_date) FROM mydata GROUP BY source_id;

the query takes about an hour to complete.

Is this normal performance for such a large table, even with an index? How can I reduce this query time?

With only a few dozen distinct values of source_id, you can get fast execution on the index you built, using a loose index scan, aka a "skip scan".  Unfortunately PostgreSQL does not plan those automatically, so you have to force it into one, by using a recursive query.

with recursive t as (
   select min(source_id) as col from mydata
   union all
   select (select min(source_id) from mydata where source_id>t.col)
      from t where t.col is not null)
select
  col,
  (select min(last_updated_date) from mydata where source_id=col),
  (select max(last_updated_date) from mydata where source_id=col)
  from t;

Even if you don't resort to this, just doing the query as you originally wrote it should not take nearly an hour.  But without seeing an explain and an explain analyze, there isn't much more that can be said on that.


Logshipping is failing intermitently Client has setup logshipping one of the database. Out of 3 steps in logshipping - backup and copy works without any issues, but restore fails intermittently giving 'out of sync' error.
When I pulled backup/restore history using query, I am getting some entry with 'NUL' for physical device name as shown below.

Is that is causing issue? How to resolve this issue?
Thanks
Naveen

it seems that your log shipping is working fine. Just remove additional log backups from maintenance plans since they can break log shipping.


Composite Attributes in apex oracle I am learning database in apex oracle.
Now I am trying to create table.
I Have composite attributes, but I cannot implement on db.

  CREATE TABLE PERSON
(
    FIRSTNAME VARCHAR2(15) NOT NULL,
    LASTNAME VARCHAR2(15)  NOT NULL,
    SSN NUMBER(11) NOT NULL,
    BIRTHDATE DATE,
    AGE NUMBER(3),
    GENDER VARCHAR2(6),
);

Not all logical attributes have to be present in the physical model, as you can easily produce them while querying data:

SELECT firstname || ' ' || lastname AS fullname
FROM person

If you insist on implementing it as a column, you could use a generated (computed) column:

CREATE TABLE PERSON
(
FIRSTNAME VARCHAR2(15) NOT NULL,
LASTNAME VARCHAR2(15)  NOT NULL,
FULLNAME VARCHAR2(31) GENERATED ALWAYS AS (FIRSTNAME || ' ' || LASTNAME) VIRTUAL,
...

For completeness I should mention that you could also create a user-defined object type with FIRSTNAME and LASTNAME as its attributes, but I personally think that you shouldn't go that route for portability and maintainability reasons.


Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range Based on the date_entered column, which is a data_type of 'DATE' how would I parse out the data to pull the time.

Values in the DATE_ENTERED field (DATA_TYPE is DATE) looks like this 5/15/2015 8:43:04 PM

I need to run queries based on when DATE_ENTERED is between 05/15/2015 8:00 AM and 05/15/2015 10:00 AM

I then need to incorporate that into a PL/SQL script that will run every 2 hours with the last two hours as the filter. My best explanation of this would be where it grabs the current date and time, and pulls 2 hours back, every 2 hours. This will allow it to run at 8am, 10am, 12pm, 2pm etc and have the last 2 hours of new entries in the query. For instance 10am run time will include results for items entered in from 8:00am until 10:00am

It sounds like you want

WHERE date_entered BETWEEN sysdate - interval '2' hour
                       AND sysdate

Assuming date_entered never contains future dates

WHERE date_entered >= sysdate - interval '2' hour

Depending on exactly what you need (and why you're running the query), you may want to truncate the date to the hour or minute so that you're always comparing against data from the top of the hour

WHERE date_entered >= trunc(sysdate - interval '2' hour, 'HH24')

Depending on why you are doing this, however, keep in mind that you may miss data if you're running every 2 hours.  If I insert a row at, say 3:59:59 pm, your job starts runs at 4:00:00 pm (looking for data between 2 and 4) and at 6 pm (looking for data between 4 and 6), and I commit at 4:00:01 pm, then neither execution of the job will return this row.


SQL Server 2012 - where is the table in dbo? I am trying to insert something into the dbo.Documents table.
 After the query is run, I can't find the Documents under the TSQL2012 database.
 I created TSQL2012 for exercise purpose.

I couldn't find the table Documents created in the TSQL2012 database. Please give me some advises on where this table is created. Thanks!

Here is the query:

 INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Additivity of Measures',
 N'docx',
 N'Additivity of measures is not exactly a data warehouse design problem.
   However, you have to realize which aggregate functions you will use
   in reports for which measure, and which aggregate functions
   you will use when aggregating over which dimension.',
 bulkcolumn
FROM OPENROWSET(BULK 'C:\Users\admin121\Downloads\9780735666054_Files\Chapter 06\AdditivityOfMeasures.docx',
                SINGLE_BLOB) AS doc;

Oftentimes this problem stems from one of two scenarios:

Either you created the table in the wrong database, or you never created the table at all (user error or just forgetfulness. It happens to us all).

What you need to do is look through each of your databases for your table.  You can do this through the sys.tables system catalog view in your instance's databases. Usually in SSMS people forget to change the database context from master to their desired database, causing DDL to execute in master. Regardless, master is a good first place to look.


Where to put ReportServer and ReportServerTempDB databases? I have a SQL Server 2008 R2 database server that hosts a data warehouse database.  On this server is also installed SQL Server Reporting Services, and its two databases ReportServer and ReportServerTempDB.

For performance reasons I want to separate the data warehouse database and the Reporting Services.  I have another server that is available to install SSRS onto.  Both servers are virtual and so their specifications are easily changed, or other servers could be 'created' if necessary.

What I can't get my head around is where the best place would be to host the ReportServer and ReportServerTempDB.  I can see three options:

leave them with the data warehouse database
put them on the server that will have Reporting Services installed on it
create another server specifically to host those two databases.

With option 1 I worry that separating the Reporting Services from its databases will either impact the data warehouse server, or will impact the reports.  With option 2 I worry that installing the database engine on the server will impact the Reporting Services server performance, and with option 3 I worry that I'm wasting resources creating a server that will hardly do anything, and also impacting the reports by separating the Reporting Services from its database.

I think the key piece of information I need is to know which part of Reporting Services is the most resource intensive.  If it's the Reporting Services application then I can leave the databases with the data warehouse.  If it's the databases that are resource intensive then I need to either put them with the Reporting Services application or create a new server.

Can anybody shed some light please?

Thanks!

SSRS Planning a Deployment would be a good article to read through.

You can read specifically on the SSRS databases here. As it states:

  A report server is a stateless server that uses the SQL Server
  Database Engine to store metadata and object definitions. A Reporting
  Services installation uses two databases to separate persistent data
  storage from temporary storage requirements. The databases are created
  together and bound by name. By default, the database names are
  reportserver and reportservertempdb, respectively.

You general, with large reporting requirements, will run SSRS databases on one server (shared SQL Server instance) and then the SSRS components on another. Keep in mind though how licensing works, you will have two licensed SQL Server servers with this type of setup. I normally see the report server databases reside on the same server as a data warehouse. I have not seen any issue with the SSRS databases being on a shared instance with the data warehouse. As it states in the article they are only storing information for SSRS (execution log, schedules, report definitions, etc.).

The "meat" of what SSRS does resides with the SSRS component services. SSRS processes the data the report is requesting and then also works out rendering it, all of this is resource intensive if there is a high load. It will depend on what your specific needs are if it justifies a standalone server. I generally will see SSRS installed on the web server with the application that uses the reports.

To determine if I need a standalone report server I would probably consider things like number of reports (many small or many large), the frequency (internal report server for monthly reporting or serving up user reports for external web application). You may also look at the report design or standard used by the developers, if the reports do a high amount of data processing on the report side, versus letting the database engine do it.


made condition on column in view I have made a view in which i use function to get a column something like that

dbo.GetFinalLetter(Id, CASE
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText

so i want to put an other condition that only those results been shown that have text in that column for that purpose i used

where LetterText <> null

where LetterText not null

but it shows error

You can try to move your function call to an OUTER/CROSS APPLY and then use the APPLY result to check your value:

SELECT <your columns>
FROM <your tables>
    CROSS APPLY
    (
        SELECT dbo.GetFinalLetter(Id, CASE
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText
    ) AS x
WHERE x.LetterText IS NOT NULL ;

OR you can also use a subquery of the full query:

SELECT <your columns>
FROM
(
    SELECT <your columns>,
        dbo.GetFinalLetter(Id, CASE
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText
    FROM <your tables>
) AS t
WHERE LetterText IS NOT NULL ;

The way to do that may depend on the data volume of the tables.

Hope this helps.


Correct place to put very large text field for mysql/mariadb I have an app where a queue is being processed, Each time an entry is processed the result is stuck in a separate table. The queue table gets inserts and updates, the result table inserts only. The result table has a text column containing a complete context of how the queue entry was handled encoded in JSON, average length 28,000 characters.

The table is now about 800k rows and 37gigs in size and really slow to deal with when running aggregates. But this particular field with the JSON data is rarely used - kept there just in case there is some important data not parsed into it's own field already.

Moving that field to another table is not out of the question if that is the right way to go, but it will require multiple application changes. I've moved the data from MySQL 5.1 into MariaDB 10.0.14 and put it in TokuDB but found no benefit in speed, but it's compression brought the size down by about 10 gigs though.

I've made no other significant changes to any settings. If there is any tip on how to handle this without making structural changes that would be great.

I'd suggest you split this large column off into its own table.  While you're there take out any other columns which are only used once in a blue moon.  (The smaller your row the faster your queries will run.)

I'll take your current table as MyData.  Rename it to, say, MyData_Crucial.  Let's call the new table MyData_Sometimes.  Create this view:

create view MyData
select
    <all the columns>
from MyData_Crucial
inner join MyData_Sometimes
    on <primary key> = <primary key>

Now, assuming your optimizer is at least a little bit clever, you will not need to change your application code at all.


ERROR: check constraint is violated by some row I've got the following error:

ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row

Query:

ALTER TABLE integrations.tax_aggregates
DROP COLUMN IF EXISTS myob_id,
ADD COLUMN myob_id integrations.FOREIGN_IDENTIFIER;
COMMENT ON COLUMN integrations.tax_aggregates.myob_id IS 'Foreign key for MYOB';

ALTER TABLE integrations.tax_aggregates DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed,
ADD CONSTRAINT cc_at_least_one_mapping_needed CHECK ((("qb_id" IS NOT NULL) :: INTEGER +
                                                      ("xero_id" IS NOT NULL) :: INTEGER +
                                                      ("freshbooks_id" IS NOT NULL) :: INTEGER +
                                                      ("myob_id" IS NOT NULL) :: INTEGER +
                                                      ("ppy_id" IS NOT NULL) :: INTEGER) > 0);

                                                      DROP INDEX IF EXISTS integrations.ix_tax_aggregates_myob_ids_ids;
CREATE INDEX ix_tax_aggregates_myob_ids_ids ON integrations.tax_aggregates USING BTREE (myob_id)
  WHERE myob_id IS NOT NULL;

What am I doing wrong? How can I solve the problem?

UPDATED:

Query:

ALTER TABLE integrations.accounts
DROP COLUMN IF EXISTS myob_settings,
ADD COLUMN myob_settings JSON;

ALTER TABLE integrations.accounts DROP CONSTRAINT IF EXISTS cc_at_least_one_setting_needed,
    ADD CONSTRAINT cc_at_least_one_setting_needed CHECK (("qb_settings" IS NOT NULL) or
                                                          ("xero_settings" IS NOT NULL) or
                                                          ("freshbooks_settings" IS NOT NULL) or
                                                          ("myob_settings" IS NOT NULL) or
                                                          ("ppy_settings" IS NOT NULL));

Select results:

SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL AND xero_settings IS NOT NULL and freshbooks_settings IS NOT NULL AND myob_settings IS NOT NULL AND ppy_settings IS NOT NULL

It returns 0 rows

SELECT * FROM integrations.accounts WHERE qb_settings IS NULL OR xero_settings IS NULL OR freshbooks_settings IS NULL OR myob_settings IS NULL OR ppy_settings IS NULL;

RETURN (59 rows):

"account_id"|"global_settings"|"qb_settings"|"xero_settings"|"freshbooks_settings"|"myob_settings"|"ppy_settings"

"30374"|""|"{""id"":""1412494720"",""active"":true,""settings"":{""secret"":""iisCCNSiH4jmRArWUPi0rxa9LntQdAA3kc7lbLtf"",""token"":""qyprdigrVpUijVKDxJ72iBZrnh4tvlwMA1QuVxxyPSgLIz4j"",""expires"":""2016-01-31 04:34:37""},""plan"":""QuickBooks Online Essentials""}"|""|""|""|""

ERROR:  check constraint "cc_at_least_one_setting_needed" is violated by some row

You have NULLs that violate the CHECK CONSTRAINT.

To verify that this is indeed the problem, run this SQL.

SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL OR
xero_settings IS NOT NULL OR... (fill in the fields that correspond to
those in the CONSTRAINT).

This will give you all the records that have fields which have a NULL value which violate the CONSTRAINT.

From your SQL, you have 59 such records - that's why you're getting the error.

What you need to do to rectify this is to run SQL similar to the following:

UPDATE My_Table SET My_Field = 0 WHERE My_Field IS NULL.

Obviously it is up to you to ensure that the values that you set correspond to your system's business logic and other system requirements.


How can I decrypt data on a replicated MSSQL database? I have a database which uses a symmetric key to encrypt a credit card field. There is a trigger on the credit_card field which runs every time it is updated, to encrypt the contents, and save it to another field called credit_card_encrypted and then it wipes the contents of credit_card.

I have recently setup replication on the table, to another instance of SQL Server.
Everything works fine except, I am unable to decrypt the data on the replicated server.

I have imported the certificate and the key and I have created a symmetric key however, whenever I try to decrypt the credit cards, it just returns NULL.

I have tried to manually add a value to the credit_card field on the replication server, and then I manually encrypted it, and I was actually able to successfully decrypt it.

So why can I not decrypt the data that is replicated?

Also, I can compare the varbinary data of the encrypted card and the contents looks exactly the same on the main server &  the replication server.

e.g: 0x000CE24D0120A349PPL29D6BFE70C54E0100000064E3380AA4EC04A4B4E959535798696E81502A063617B21CFD75FAFF93866D47603543A5D6EBECDF5F8C0D23D8CCL982

I have imported the certificate and the key and I have created a symmetric key however, whenever I try to decrypt the credit cards, it just returns NULL.

If the symmetric key you are talking about is one you've manually created in the user database then you'll need to create it with the same initialization vector as the previous one by specifying a Key_Source and the same algorithm. If you haven't done this (or restored the database containing the same key) then that's your issue.

Here is a walk through to show you how to create the same symmetric keys in multiple databases.


SQL Server 2008R2 Migration to new Platform - What Steps Required? We are migrating our SQL Server 2008R2 instance from one Windows Server platform to another. SQL Server will remain as 2008R2. The new plaform will have more disk space, memory and CPU.

I am doing up a tasklist of what is required to migrate SQL Server. Below is an outline of what I think are the steps involved.
Are these "best practice" or am I missing some requirements?

Thanks!

1) Backup and Restore Databases - Is this the best option? Use Ola Hallengren Script

2) Migrate Logins - use Microsoft KB? kb/918992

3) Migrate Credentials/Certificates - what is required for this?

4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)

5) Migrate SSIS Packages - (How to do this?)

6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)

7) Recreate assemblies

8) Recreate Linked Servers

NOTE: System Databases - do these need to be migrated?

Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time:

1) Backup and Restore Databases - Is this the best option?

It's an option. If you need the downtime to be less, you can use mirroring or log shipping (or do it by hand) to keep the databases in sync and then migrate during a fast downtime or cutover.

Please note that system databases cannot be log shipped or mirrored and would need to either be frozen or copied right before the cutover.

2) Migrate Logins - use Microsoft KB? kb/918992

If you restore the master database to the new instance, no migration should be needed. All server level logins are stored in the master database.

3) Migrate Credentials/Certificates - what is required for this?

If this is inside of SQL Server, these would be held in their respective databases (and possibly master as well). The one difference would be the service master key (SMK) that would change. You make want to back that up and restore it on the new server if you're relying on automatic key decryption.

4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)

If you restore the msdb system database, all agent jobs will be held in it. There would be no need to script->restore.

5) Migrate SSIS Packages - (How to do this?)

If the SSIS packages are on the filesystem (not default) it would be trivial to create the same location on the new server and copy. By default the SSIS packages are held in the msdb system database and restoring it would get you to the same place, just like #4 and previous.

6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)

This is also stored in msdb. See #4, #5.

7) Recreate assemblies

These live in their respective databases. If backup and restore is used, this should not be a problem. Any assemblies outside of SQL Server would need copied to the new server.

8) Recreate Linked Servers

These also live in the master database, see #2.


Convert Column values into row values with new row headers Starting table

Col1|col2|col3
---------------
1   |EDTU|Value1
2   |EDTP|Value2
3   |aaaa|value3
4   |bbbb|value4
5   |cccc|value5

I only need values for EDTU and EDTP in below format with new row header

Expected result:

XXXX  |YYYY
----------
value1|value2

Note :- using where clause if col2 = 'EDTU' we get Value1.

Assuming then, that you are going to add (or derive) another column for DatabaseName (or source name) then this should work for you.

DECLARE @T TABLE
    (
    Col1 INT NOT NULL
    , Col2 VARCHAR(10) NOT NULL
    , Col3 VARCHAR(10) NOT NULL
    , DatabaseName VARCHAR(20) NOT NULL
    )

INSERT INTO @T
(Col1, Col2, Col3, DatabaseName)
VALUES (1, 'EDTU', 'Value1', 'FirstDB')
    , (1, 'EDTP', 'Value1', 'FirstDB')
    , (1, 'Other', 'Value1', 'FirstDB')
    , (1, 'EDTU', 'Value5', 'SecondDB')

;WITH CTE_Source AS
    (
    SELECT S.Col2
        , S.Col3
        , S.DatabaseName
    FROM @T AS S
    )
SELECT DatabaseName
    , EDTU
    , EDTP
FROM CTE_Source AS S
    PIVOT (MAX(Col3) FOR Col2 IN (EDTU, EDTP)) AS P


SQL Server: Can I log ship from SQL Server 2014 --> 2016? SQL Server 2014 and 2016, all of them Standard Edition.

We are migrating to a new SQL Server. We normally do this via log shipping (this lets us have a small cutover window, because we just need to restore final logs, take the new db out of recovery, mod connection strings and we are done...)

We would like to migrate to SQL 2016 in this process.

Poss to log ship from 2014 to 2016?

You can always log ship UP as it's a basic supported upgrade process.  you can't log ship DOWN.  The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014.  You will need to setup a process to get the data in sync such as replication, red gate data compare, etc.


Web application using SQL Server 2008 starts hanging We have a custom web application that uses SQL Server 2008 (10.50.1600). It's worked fine for years, but lately web pages that return data "instantaneously" have begun showing the "endless swirly blue thing" in IE. Something is causing queries to run slower, lock, contend or something else. Doesn't seem to be deadlocking, as I am not seeing deadlock errors at the user level, which I have (very rarely) seen (and know they will trigger a specific error).

If users close out of IE completely and go back in, they usually are able to do the previous work without this hanging behavior (well, until such time as the hang happens again).

How would you begin to diagnose the source of this issue?

I would check at logs,
1) IIS Logs
2) Windows Logs
3) SQL Server Logs

I think logs usually gives away a hint in case of any issue.


SQL, category tree with multiple parents I need to make a category tree where some leafs can have multiple parents, for exaple

g
 \--b
 \--a
c
 \--d
 \--a

above category 'a' has two parents(g and c)

My first attempt was to create the following table

create table catalogcategories2 (
  id serial,
  name text not null,
  pid int null,
  primary key(id,pid),
  CONSTRAINT catalogcategories_fk FOREIGN KEY (pid)
  REFERENCES catalogcategories2(id)
);

where both parent id and id are primary keys but I get the following error

  there is no unique constraint matching given keys for referenced table "catalogcategories2"

Some reflections, a separation of tree[1] structure and content is most likely the way to go. I.e. remove name from catalogcategories2. Now nodes that don't have a parent does not have to exist in this table. I.e. you can make pid not null.

The current foreign key is invalid since the primary key which it references consists of two columns.

A sketch:

CREATE TABLE CATEGORIES
(  ID ... NOT NULL PRIMARY KEY
,  NAME ... NOT NULL
,  <additional attributes> );

CREATE TABLE CATEGORY_DAG -- [1]
(  ID ... NOT NULL
,  PID ... NOT NULL
,    PRIMARY KEY (ID, PID)
,    FOREIGN KEY (ID) REFERENCES CATEGORIES (ID)
,    FOREIGN KEY (PID) REFERENCES CATEGORIES (ID)
);

[1] What you describe is a Direct Acyclic Graph (DAG), not a tree.


How to change the current database in an SQL Query window in SSMS? I'm trying to create a SQL script that creates a new server login in the master database, and then creates a corresponding user in an existing database.
If the 'USE [database_name]' appears more than once in the query editor, it results in two errors:

Msg 40508, Level 16, State 1, Line 4 USE statement is not supported to
switch between databases. Use a new connection to connect to a
different database.
Msg 40508, Level 16, State 1, Line 11 USE
statement is not supported to switch between databases. Use a new
connection to connect to a different database.

I've considered turning on SQL CMD mode and using the :CONNECT command, but I don't want to connect to a different database server, just change the database. Connecting to a different database (or even the same one), would require me to embed a username and password in the script as well, which is not desirable.  I just want to open the SQL script connected to the right server, and be able to switch between databases.

Read this link

This issue occurs because Azure SQL Database does not support the "use" command.

You have to install the latest cumulative update for SQL Server


When should I use Full-Text Search? When should I use Full-Text Search instead of using a LIKE search, such as

SELECT * FROM myTABLE WHERE myCOLUMN LIKE '*something'

From MSDN - Full Text Search Overview:

"Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.

Full-text search is applicable in a wide range of business scenarios such as e-businesses—searching for items on a web site; law firms—searching for case histories in a legal-data repository; or human resources departments—matching job descriptions with stored resumes. The basic administrative and development tasks of full-text search are equivalent regardless of business scenarios. However, in a given business scenario, full-text index and queries can be honed to meet business goals. For example, for an e-business maximizing performance might be more important than ranking of results, recall accuracy (how many of the existing matches are actually returned by a full-text query), or supporting multiple languages. For a law firm, returning every possible hit (total recall of information) might be the most important consideration."

Comparison of LIKE to Full-Text Search

"In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned."

So the main difference is the type of search that they do: full text is about word search, while LIKE is about character search. I would personally use full text search on tables where I have big amounts of human text - usually big columns (big descriptions, exceeding the standard 8k characters) in big tables (millions of rows). Need to keep in mind the added administration for FTS (create catalogs, indexes, update them when needed).


mysql "Unsafe statement" I get a lot of following messages

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction. Statement:

I don't really understand why this happens. According to messages, it happens because in the code I try to INSERT and UPDATE Innodb table and MyISAM table. Is it right?

Is this dangerous? What's the consequences of this error.

How can I solve it. Can it be solved without changing the code?

Currently, it looks like by default, mysql uses BINLOG_FORMAT = STATEMENT. It means that replication on slaves runs like mysql statements? What's the preferable option for BINLOG_FORMAT?

Mysql version is 5.5.24.

Thanks

You can set any of these three BINLOG-FORMAT :

STATEMENT causes logging to be statement based.
ROW causes logging to be row based.
MIXED causes logging to use mixed format.

The default binary logging format depends on the version of MySQL you are using:

For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.
For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.

You have to make sure that:

Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system variable is not replicated; when using MIXED or ROW logging mode, it is replicated but is ignored by the slave.) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.
When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication.

Also, this bug report is related about problem you have mentioned here :

http://bugs.mysql.com/bug.php?id=53259


Specify file target filename dynamically I need to create an Extended Event session to capture all the executed queries while my server is running and save them in a file under a folder with the database name.

DECLARE @file varchar(50);
declare @path nvarchar(250);
SET @file = 'D:\XE\ ' + DB_NAME()
set @path = 'D:\XE\ ' + DB_NAME() + 'day';
EXECUTE master.dbo.xp_create_subdir @file
CREATE EVENT SESSION newSession1
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack,
     sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username )
    WHERE ( sqlserver.database_name = 'School' )
    ),
ADD EVENT sqlserver.sql_statement_starting
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack,
     sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username )
    WHERE ( sqlserver.database_name = 'School')
    )
ADD target package0.event_file (SET filename = @path) -- problem
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

This code shows an error in set Filename = @path.

You need to create the event session in dynamic SQL (or build the whole command in C#).

DECLARE @file nvarchar(500), @path nvarchar(500), @db sysname = DB_NAME();

SET @file = RTRIM(N'D:\XE\ ') + @db;
SET @path = @file + N'day';

EXEC master.dbo.xp_create_subdir @file;

DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION ' + @db + N'_Session1
  ON SERVER
  ADD EVENT sqlserver.sql_statement_completed
  (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
            sqlserver.client_hostname, sqlserver.username
  ) WHERE ( sqlserver.database_name = N''' + @db + N''' ) ),
  ADD EVENT sqlserver.sql_statement_starting
  (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
            sqlserver.client_hostname, sqlserver.username
  ) WHERE ( sqlserver.database_name = N''' + @db + N''')
)
ADD target package0.event_file
(
  SET filename = N''' + @path + N'''
)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);';

EXEC master.sys.sp_executesql @sql;


different db stats in MongoDB replica set I have a MongoDB (3.2.6) replica set with two nodes and an arbiter. The secondary node was out for quite a long time and was recently brought back up with an initial sync.
So at the moment the rs.status() is as follows:

rs.status()

{
    "set" : "anonymizedReplicaSet",
    "date" : ISODate("2017-05-24T07:33:08.629Z"),
    "myState" : 2,
    "term" : NumberLong(-1),
    "syncingTo" : "anonymized01:27017",
    "heartbeatIntervalMillis" : NumberLong(2000),
    "members" : [
        {
            "_id" : 1,
            "name" : "anonymized01:27017",
            "health" : 1.0,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 157061,
            "optime" : Timestamp(6423603132856545, 12785),
            "optimeDate" : ISODate("2017-05-24T07:40:52.000Z"),
            "lastHeartbeat" : ISODate("2017-05-24T07:33:06.790Z"),
            "lastHeartbeatRecv" : ISODate("2017-05-24T07:33:06.790Z"),
            "pingMs" : NumberLong(0),
            "electionTime" : Timestamp(6415474245823889, 1),
            "electionDate" : ISODate("2017-05-02T09:56:38.000Z"),
            "configVersion" : 10
        },
        {
            "_id" : 2,
            "name" : "anonymized02:27017",
            "health" : 1.0,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 157062,
            "optime" : Timestamp(6423603141446477, 10075),
            "optimeDate" : ISODate("2017-05-24T07:40:54.000Z"),
            "syncingTo" : "anonymized01:27017",
            "configVersion" : 10,
            "self" : true
        },
        {
            "_id" : 3,
            "name" : "anonymizedas:27017",
            "health" : 1.0,
            "state" : 7,
            "stateStr" : "ARBITER",
            "uptime" : 157061,
            "lastHeartbeat" : ISODate("2017-05-24T07:33:06.790Z"),
            "lastHeartbeatRecv" : ISODate("2017-05-24T07:33:07.248Z"),
            "pingMs" : NumberLong(0),
            "configVersion" : 10
        }
    ],
    "ok" : 1.0
}

However, I noticed that the two nodes do not agree on their stats, e.g., when I issue db.stats() simultaneously to the two servers I get:

Primary Node db.stats()

{
    "db" : "mydb",
    "collections" : 8,
    "objects" : 43933967,
    "avgObjSize" : 575.327071124718,
    "dataSize" : 25276400557.0,
    "storageSize" : 11909304320.0,
    "numExtents" : 0,
    "indexes" : 12,
    "indexSize" : 997511168.0,
    "ok" : 1.0
}

Secondary Node db.stats()

{
    "db" : "mydb",
    "collections" : 8,
    "objects" : 44016455,
    "avgObjSize" : 576.927973845236,
    "dataSize" : 25394324199.0,
    "storageSize" : 9882480640.0,
    "numExtents" : 0,
    "indexes" : 12,
    "indexSize" : 901128192.0,
    "ok" : 1.0
}

Shouldn't the number of objects on the two nodes agree? In addition, I receive different results when issuing mydb.mycollection.count() commands. Is this normal behavior? Or is something wrong with the syncing of the two nodes?

Logically answer is YES.

When I tested on our multi terabyte DB's, I got differences between replica set members, however this was measuring mistake, error on the procedure. f.ex. db.stats() is not good command, because RS node can have db.system.profile -collection, what is NOT replicated to other nodes.

How to do it right (do these on all replica set nodes):

Stop all write traffic to MongoDB, you can do that by closing all clients OR giving (at Primary) command db.fsyncLock()
select right DB (use xyz) and give command db.setSlaveOk(true)
Don't use db.collection.find().count() or db.collection.count(), use command db.collection.find({_id:{ $gt: MinKey, $lt:MaxKey} }, {_id:1}).itcount() to count how many documents there is
After you have done these itcount() commands on multiple collections and you are happy (or not) to result.. Don't forget to give (at primary) command db.fsyncUnlock()

we had maintenance break and I did this counting procedure during it and got (this time) right result. Every RS node had same count of documents. ;-)


MySQL / MariaDB : Many indexes vs. One multi-column index I need to optimize my MariaDB tables index.
I've read a lot of very interesting things on StackExchange, but I'm not sure to have the answer for my case.

In my case, I have a table named TableA which contains more than 40 millions records (stats purposes) with these columns :

ID (PK)
A (INT)
B (INT)
C (INT)
D (INT)

I make different queries on this table, with many of the 4 columns in the WHERE clause.
For example, I can have these columns in WHERE clause of the same query :

A, B and C
A and B
A and D
A, C and D
B and D
B, C and D
C and D
only A
only B
etc.

I can encounter every case.
What would be the best way to index that table which count more than 40 millions records ?
Should I make only one INDEX with all 4 columns ? Or 4 different indexes ?
This table is used everytime in my app, and contains sensitive data...(for stats purposes).

Thank you for your help !

Joffrey

There is no perfect answer.  A possibly good answer is...

First, are the columns compared with = constant?  Or a "range", such as > constant?  For now, I will assume only =.

How selective are the values?  True/false values are not; let's ignore them.

Assuming the 'worst' case (always =, all are selective), let's take the 6 permutations of two columns:

INDEX(a, b) -- for (a), (a,b), (a,b,c), (a,b,d), (a,b,c,d)
INDEX(a, c)
INDEX(a, d)
INDEX(b, c)
INDEX(b, d)
INDEX(c, d)

This list will handle all the one- and two-column tests efficiently, and at least help when you use 3 or 4 columns.

If some column is always used in a range, then never put anything after it in the INDEX.  If, for example, b is always tested via a range, change the 4th and 5th to (c,b) and (d,b)

Other tips:

You say INT.  That allows 4 billion values and takes 4 bytes.  Use a smaller datatype.  40M distinct values needs INT UNSIGNED, but if you have something with only a million distinct values, consider MEDIUMINT UNSIGNED (3 bytes, 0..16M).  Etc.  The rationale is that making the table and indexes smaller helps decrease I/O, hence improves speed.
Do you need id?  Or is some combo of the columns unique and could become the PK, there by elimination id?
If there are common patterns, extend some of the indexes.  For example, if usually when users test a and c, they also test b, then change (a, c) to (a, c, b).
Use InnoDB.
If any column is "low cardinality", then no index will be used when the WHERE clause mentions only that column; live with it.  That column, when combined with others, works fine.
Do not over-normalize.  I hope that a,b,c,d are not normalizations of floats or datetimes.  Note how this hooks into my comments about "ranges".

If you would like to discuss further, please provide some extra clues about the data and queries involved.  (Note that I had to do hand-waving about flags and ranges.)


Oracle DB: Can one db file be shared between table space Can one db file be shared between table space?

I am deleting temp table space as below,

Get all the files in  table space:

SELECT FILE_NAME
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = 'TEMPRM_TEMP';

Drop all the files in the table space:

ALTER TABLESPACE TEMPRM_TEMP
 DROP TEMPFILE '/tmp/TEMPRM/create/TEMPRM/datafile/o1_mf_temprm_t_bw3yo9lv_.tmp';

Drop the actual table space:

DROP TABLESPACE TEMPRM_TEMP INCLUDING  CONTENTS AND DATAFILES;

Are there any harm with this procedure ?

From the Oracle 11g Documentation:

  A datafile can be associated with only one tablespace and only one
  database.

So you don't have to worry about losing data from another tablespace.


How to query from the result of a changed column of a table in postgresql I am working with Presto on Amazon Athena. I have a string time column in a table and now I want to change that time to date time type and then query data for selected dates.

Is there a direct way to do so? One way I could think of is

1) add a new column
2) insert values into it with converted date
3) Query using the new column

Here I am stuck with the 2nd step with INSERT so need help with that

  ALTER TABLE "db"."table"
    ADD COLUMN new_date timestamp

-- Need correction in select statement that I don't understand
    INSERT INTO "db"."table" (new_date)
    (SELECT new_date from_iso8601_date(substr(timestamp,1,10))

Could some one help me with correction and if possible a better way of doing it?

I was able to figure out as

select *
from
(
  SELECT from_iso8601_date(substr(timestamp,1,10)) as day,*
  FROM "nds"."unacast_sample_august_2018"
 )
WHERE  day > date_parse('2018-08-26', '%Y-%m-%d')
 limit 10


MySQL replication as both master and slave (replication loop?) I've got two MySQL servers, one as a master in the replication process and the other as the slave. I was wondering if anything bad would happen if I made the current master a slave to the current slave, and the current slave a master to the current master. So basically, both servers would be replicating to and from each other.

I saw some questions and posts on other sites about 'replication loops', which is how I would describe my question; but these posts seemed to be explaining something completely different.

Terminology

What you're describing is a common set up that may be variously described as master-master replication, dual-master, active-active, bidirectional, or a few other terms.

Good reasons not to do it

The team who wrote High Performance MySQL offer this pithy summary of master-master replication - specifically, "active-active" replication:

  In general, allowing writes on both servers causes way more trouble than it’s worth.

Baron Schwartz, one of the authors, offers a slightly more blunt opinion on his blog.

  Save yourself grief, work, and money. Never write to both masters.

There are many things that can go wrong if both servers are updating data at the same time, including (but by no means limited to):

If something goes wrong with replication - even a temporary network glitch, you'll likely end up with a split brain and it may be impossible to determine which data is "correct"
If AUTO_INCREMENT MySQL settings are wrong, it's easy for data collision to take place
Row locking no longer offers any protection - data can be manipulated on one master even while a transaction has it locked on another

Good reasons to do it - carefully

The most common reason for master-master replication is to so that you can very quickly switch the "current" master if something goes wrong with one of your servers.

The common way to implement this is to set up a barrier that ensures that only one of your servers is every written to by your application servers. This is usually a hardware load balancer, or software load balancer like HAProxy.

The authors of High Performance MySQL (mentioned above) describe this as "active-passive" master-master replication.

As long as the barrier is robust, you are fairly well protected from all of the issues described above. This may be supported by setting read_only on the "passive" server for an extra layer of protection.

A well-built master-master setup can offer you some amazing flexibility - you can do some really complex table or server maintenance on your passive server, wait for replication to catch up, then migrate your load balancer over to point at your shiny, reconfigured environment with almost no service downtime. This sort of thing does need to be planned carefully, though, and a lot can go wrong.


How to make SSMS Properties Window coincide with object selected in Object Explorer The Properties Window in SSMS seems lame to me. All it shows is the current connection properties. I would like to be able to click on an object in the Object Explorer (e.g. a table or a column) and instantly see the corresponding properties displayed in the Properties Window. Is there some setting in Options that would make this possible?

Now, if I switch to a database diagram and select a box (Table) or field (Column), the Properties Window updates accordingly. But I rarely interact with DB diagrams. I want this functionality while I am working in the Query Designer. Is there anyway to do this?

There's nothing out of the box that will let you do this.

This is kind of confusing... because the Properties window never shows properties about anything in Object Explorer. If you right-click | Properties on anything in Object Explorer, you always get a pop-up dialog box. The Properties window only shows properties for objects that appear in select MDI-style windows, such as query windows.

Having said that, even though there's no way to do this, I'm not even sure it's such a good idea.

In order to return that kind of information to the UI, SSMS has to hit the server with a metadata query. SSMS is a data-driven application; it's just special in that it operates on SQL Server itself. IntelliSense works the same way. If you're on a production server, you probably don't want this kind of thing enabled because every time you touch a different node in Object Explorer (which is very frequently), it will have to hit the server to retrieve the data. And then you get into a debate about how to turn it off, and ......