How to implement a Set based algorithm/UDF

With regards to methodology, I believe you are barking up the wrong b-tree ;-).

What we know:

First, let's consolidate and review what we know about the situation:

Somewhat complex calculations need to be performed:

This needs to happen on every row of this table.
The algorithm changes frequently.
The algorithm ... [uses] values from some columns to manipulate other columns
Current processing time is: 7 hours

The table:

contains 800,000 rows.
has 38 columns.

The application back-end:

is Microsoft Excel.
uses VBA (Visual Basic for Applications)** as the language.

Database is SQL Server 2014, Enterprise Edition.
There is a Stored Procedure that is called for every row:

This takes 50 ms (on avg, I assume) to run.
It returns approximately 4000 rows.
The definition (at least in part) is:

SELECT AVG([AD_Sensor_Data])
           OVER (ORDER BY [RowID] ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING)
           as 'AD_Sensor_Data'
FROM   [AD_Points]
WHERE  [FileID] = @FileID
ORDER BY [RowID] ASC

What we can surmise:

Next, we can look at all of these data points together to see if we can synthesize additional details that will help us find one or more bottle necks, and either point towards a solution, or at least rule some possible solutions out.

The current direction of thought in the comments is that the major issue is data transfer between SQL Server and Excel. Is that really the case? If the Stored Procedure is called for each of the 800,000 rows and takes 50 ms per each call (i.e. per each row), that adds up to 40,000 seconds (not ms). And that is equivalent to 666 minutes (hhmm ;-), or just over 11 hours. Yet the whole process was said to take only 7 hours to run. We are already 4 hours over the total time, and we have even added in time to do the calculations or save the results back to SQL Server. So something is not right here.

Looking at the definition of the Stored Procedure, there is only an input parameter for @FileID; there isn't any filter on @RowID. So I suspect that one of the following two scenarios is happening:

This Stored Procedure does not actually get called per each row, but instead per each @FileID, which appears to span approximately 4000 rows. If the stated 4000 rows returned is a fairly consistent amount, then there are only 200 of those grouping in the 800,000 rows. And 200 executions taking 50 ms each amounts to only 10 seconds out of that 7 hours.
If this stored procedure actually does get called for every row, then wouldn't the first time a new @FileID is passed in take slightly longer to pull new rows into the Buffer Pool, but then the next 3999 executions would typically return faster due to already being cached, right?

I think that focusing on this "filter" Stored Procedure, or any data transfer from SQL Server to Excel, is a red herring.

For the moment, I think the most relevant indicators of lackluster performance are:

There are 800,000 rows
The operation works on one row at a time
The data is being saved back to SQL Server, hence "[uses] values from some columns to manipulate other columns" [ my emphasis ;-) ]

I suspect that:

while there is some room for improvement on the data retrieval and calculations, making those better wouldn't amount to a significant reduction in processing time.
the major bottleneck is issuing 800,000 separate UPDATE statements, which is 800,000 separate transactions.

My recommendation (based on currently available information):

Your biggest area of improvement would be to update multiple rows at one time (i.e. in one transaction). You should update your process to work in terms of each FileID instead of each RowID. So:

read in all 4000 rows of a particular FileID into an array
the array should contain elements representing the fields being manipulated
cycle through the array, processing each row as you currently do
once all rows in the array (i.e. for this particular FileID) have been calculated:

start a transaction
call each update per each RowID
if no errors, commit the transaction
if an error occurred, rollback and handle appropriately

If your clustered index isn't already defined as (FileID, RowID) then you should consider that (as @MikaelEriksson suggested in a comment on the Question). It won't help these singleton UPDATEs, but it would at least slightly improve the aggregate operations, such as what you are doing in that "filter" stored procedure since they are all based on FileID.
You should consider moving the logic to a compiled language. I would suggest creating a .NET WinForms app or even Console App. I prefer Console App as it is easy to schedule via SQL Agent or Windows Scheduled Tasks. It shouldn't matter whether it is done in VB.NET or C#. VB.NET might be a more natural fit for your developer, but there will still be some learning curve.

I don't see any reason at this point to move to SQLCLR. If the algorithm changes frequently, that would get annoying have to re-deploy the Assembly all of the time. Rebuilding a Console App and having the .exe get placed into the proper shared folder on the network such that you just run the same program and it just happens to always be up-to-date, should be fairly easy to do.

I don't think moving the processing fully into T-SQL would help if the problem is what I suspect and you are just doing one UPDATE at a time.
If the processing is moved into .NET, you can then make use of Table-Valued Parameters (TVPs) such that you would pass the array into a Stored Procedure that would call an UPDATE that JOINs to the TVP table variable and is hence a single transaction. The TVP should be faster than doing 4000 INSERTs grouped into a single transaction. But the gain coming from using TVPs over 4000 INSERTs in 1 transaction likely won't be as significant as the improvement seen when moving from 800,000 separate transactions to only 200 transactions of 4000 rows each.

The TVP option is not natively available for the VBA side, but someone came up with a work-around that might be worth testing:

How do I improve the database performance when going from VBA to SQL Server 2008 R2?
IF the filter proc is only using FileID in the WHERE clause, and IF that proc is really being called per every row, then you can save some processing time by caching the results of the first run and using them for the rest of the rows per that FileID, right?
Once you get the processing done per FileID, then we can start talking about parallel processing. But that might not be necessary at that point :). Given that you are dealing with 3 fairly major non-ideal parts: Excel, VBA, and 800k transactions, any talk of SSIS, or parallelograms, or who-knows-what, is premature optimization / cart-before-the-horse type stuff. If we can get this 7 hour process down to 10 minutes or less, would you still be thinking of additional ways to make it faster? Is there a target completion time that you have in mind? Keep in mind that once processing is done on a per FileID basis, if you had a VB.NET Console App (i.e. command-line .EXE), there would be nothing stopping you from running a few of those FileIDs at a time :), whether via SQL Agent CmdExec step or Windows Scheduled Tasks, etc.

AND, you can always take a "phased" approach and make a few improvements at a time. Such as starting with doing the updates per FileID and hence using one transaction for that group. Then, see if you can get the TVP working. Then see about taking that code and moving it to VB.NET (and TVPs work in .NET so it will port nicely).

What we do not know that could still help:

Does the "filter" Stored Procedure run per RowID or per FileID? Do we even have the full definition of that Stored Procedure?
Full schema of the table. How wide is this table? How many variable length fields are there? How many fields are NULLable? If any are NULLable, how many contain NULLs?
Indexes for this table. Is it partitioned? Is either ROW or PAGE Compression being used?
How large is this table in terms of MB / GB?
How is index maintenance handled for this table? How fragmented are the indexes? How update to date are the statistics?
Do any other processes write to this table while this 7 hour process is taking place? Possible source of contention.
Do any other processes read from this table while this 7 hour process is taking place? Possible source of contention.

UPDATE 1:

** There seems to be some confusion about what VBA (Visual Basic for Applications) and what can be done with it, so this is just to make sure we are all on the same web-page:

VBA is not Visual Basic (stand-alone language), nor is it VBScript (works in IIS and Windows Scripting Host). VBA is a reduced form of VB that is specific to Microsoft Office products, to extend their capabilities. Hence, there is no real way to simply "remove" the VBA code from Excel in order to get Excel out of the picture.
Can VBA Code Be Run Outside of MS Applications?
Run VBA outside of Office applications
Run Excel Macro from Outside Excel Using VBScript From Command Line

UPDATE 2:

One more point to consider: How are connections being handled? Is the VBA code opening and closing the Connection per each operation, or does it open the connection at the start of the process and close it at the end of the process (i.e. 7 hours later)? Even with connection pooling (which, by default, should be enabled for ADO), there should still be quite an impact between opening and closing once as opposed to opening and closing either 800,200 or 1,600,000 times. Those values are based on at least 800,000 UPDATEs plus either 200 or 800k EXECs (depending on how often the filter stored procedure is actually being executed).

This issue of too many connections is automatically mitigated by the recommendation I outlined above. By creating a transaction and doing all of the UPDATEs within that transaction, you are going to be keeping that connection open and reusing it for each UPDATE. Whether or not the connection is kept open from the initial call to get the 4000 rows per the specified FileID, or closed after that "get" operation and opened again for the UPDATEs, is far less impacting since we are now talking about a difference of either 200 or 400 total connections across the entire process.

UPDATE 3:

I did some quick testing. Please keep in mind that this is a rather small scale test, and not the exact same operation (pure INSERT vs EXEC + UPDATE). However, the differences in timing related to how connections and transactions are handled are still relevant, hence the information can be extrapolated to having a relatively similar impact here.

Test Parameters:

SQL Server 2012 Developer Edition (64-bit), SP2
Table:

 CREATE TABLE dbo.ManyInserts
 (
    RowID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    InsertTime DATETIME NOT NULL DEFAULT (GETDATE()),
    SomeValue BIGINT NULL
 );

Operation:

INSERT INTO dbo.ManyInserts (SomeValue) VALUES ({LoopIndex * 12});

Total Inserts per each test: 10,000
Resets per each test: TRUNCATE TABLE dbo.ManyInserts; (given the nature of this test, doing the FREEPROCCACHE, FREESYSTEMCACHE, and DROPCLEANBUFFERS didn't seem to add much value.)
Recovery Model: SIMPLE (and maybe 1 GB free in the Log file)
Tests that use Transactions only use a single Connection regardless of how many Transactions.

Results:

Test                                   Milliseconds
-------                                ------------
10k INSERTs across 10k Connections     3968 - 4163
10k INSERTs across 1 Connection        3466 - 3654
10k INSERTs across 1 Transaction       1074 - 1086
10k INSERTs across 10 Transactions     1095 - 1169

As you can see, even if the ADO connection to the DB is already being shared across all operations, grouping them into batches using an explicit transaction (the ADO object should be able to handle this) is guaranteed to significantly (i.e. over 2x improvement) reduce the overall process time.


How to Sum Each Enum Value in a Different Column and Don't Repeat Person's Name?

This is pretty simple to code using standard SQL if that is what you prefer.

CREATE TABLE dbo.seller
(SellerID INT,
 SellerName NVARCHAR(100));

CREATE TABLE dbo.productsold
(SellerID INT,
 SaleDate DATE,
 ProductEnum TINYINT);

-- Sample sellers
INSERT INTO dbo.seller VALUES (1, 'Frank');
INSERT INTO dbo.seller VALUES (2, 'Sally');
-- Sample transactions
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 1);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 1);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 0);

-- Query to pivot and sum
SELECT s.SellerID, s.SellerName,
  SUM (CASE WHEN p.ProductEnum = 0 THEN 1 ELSE 0 END) AS Phones,
  SUM (CASE WHEN p.ProductEnum = 1 THEN 1 ELSE 0 END) AS Recharges,
  SUM (CASE WHEN p.ProductEnum = 2 THEN 1 ELSE 0 END) AS SimCards,
  COUNT (p.ProductEnum) AS Total
FROM dbo.seller AS s
   JOIN dbo.productsold AS p ON s.SellerID = p.SellerID
GROUP BY s.SellerID, s.SellerName
ORDER BY s.SellerID;

-- Drop test tables
DROP TABLE dbo.seller;
DROP TABLE dbo.productsold;

RESULTS:

SellerID  SellerName    Phones  Recharges   SimCards  Total
1         Frank        2        2           1         5
2         Sally        2        0           2         4


Can't retrieve data of countries and regions

Since you're trying to get into the SQL way of thinking, I'll try to break this down to explain how I got to the conclusion.

To find the answer behind this search, I tried to examine the data to see what I had to work with.

SELECT * FROM bbc

I found that what we really had to deal with was Population, Name (country name) and Region.  The question stated that we need to find countries larger than others in the same region.  So, I picked a random region and examined the data there:

SELECT * FROM bbc
WHERE region = 'South Asia'

Then I sorted the data by population so that I could try to understand what we're going to compare.

SELECT * FROM bbc
WHERE region = 'South Asia'
ORDER BY population DESC

If you're following from that page, you can see that India is dramatically larger than the next sized country, Pakistan.

Next, I (misunderstanding the question), found all countries that were three times the size of any other country in that region.  However, I realized that question was really "three times the size of the smallest".  So, I came up with

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population >
        (SELECT MIN(b.population) * 3
         FROM bbc AS b
         WHERE b.region = bbc.Region)
ORDER BY population DESC

There I'm finding the population where the size is greater than three times the minimum of that region.  The correlated subquery (go research those) will limit the comparison to only select the populations in that region.

Finally, I realized that we're supposed to find the one that was three times larger than all other countries in the region (not just three times larger than ANY country in the region), so I changed my query to:

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population >
        (SELECT MAX(population) * 3
         FROM bbc AS b
         WHERE b.region = bbc.region
           AND b.name <> bbc.name)
ORDER BY population DESC

That changed the "MIN" to "MAX" and excluded countries from the subquery that are the same country as the one we're comparing.  So, when we're comparing India in the outer query, we're ignoring India in the inner query--thus finding the largest country that's Not India.

Finally, remove the "RegioN" from the outer where clause and only pull the two columns that they want (since they're picky):

SELECT name, region FROM bbc
WHERE population >
        (SELECT MAX(population) * 3
         FROM bbc AS b
         WHERE b.region = bbc.region
           AND b.name <> bbc.name)
ORDER BY population DESC


Can two transactions lock rows in the same table in parallel?

Your example is forgetting one important piece of information, which is the same row of data can be indexed more than once, by different criteria. If a second index exists with a B-Tree, and say for example the index was sorted on id descending, then you'd be able to concurrently access the row with value 100 (which would be your root node of this second index).
Now assuming by row you didn't mean table row, rather you meant a specific row location within a single index. Then yes your example above is true, when only talking about within the same index.
Note this answer is a bit high level just to explain the concepts in the context of your question, and I can't say exactly what else MySQL specifically could be doing under the hood.


Error granting access to sequence with PostgreSQL 8.4

The syntax for GRANT in PostgreSQL 8.4 is

GRANT { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCE sequencename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

You can't just exclude SEQUENCE. If you could it'd be in [], without which it's not optional.

GRANT USAGE ON SEQUENCE users_uid_seq TO myUser;

As a side note, PostgreSQL 8.4 was EOL on July 2014


I contacted Heroku after trying to find a solution for the exact same issue. Here is the response:

  Unfortunately, there is no way to upload that unaccent.rules file to
  the Postgres server that hosts your database. You can enable the
  unaccent extension, but not supply a custom rules files. If this is a
  requirement for your app to run, you will unfortunately have to find a
  workaround that doesn't rely on the unaccent.rules file.

So the shadow column might be the way to go for heroku after all.


The way I solved this was assembling these pieces:

Using a cte with running totals for AmountDue and AmountPaid
Using outer apply() to get the earliest paid date and final days overdue for each account using the running totals

DaysOverDue is either the final amount of days overdue as of the day it was completley paid off, or if not yet paid off, as of @Date which is set as current date in the query below.

PaidDate is the first date for an account where the RunningAmountPaid >= RunningAmountDue

update from comment based on additional issue not in the original question:

Added an outer apply() in the cte to check if the next row for an account had a negative value for AmountPaid and if so, added that to the RunningAmountPaid.

This cleared up the issue of an amount being marked as paid when the next transaction was a reversal of the previous payment.

rextester link: http://rextester.com/YTHG73937

test setup:

create table t (
    RowNumber int
  , AccountNumber int
  , [date] date
  , AmountDue decimal(9,2)
  , AmountPaid decimal(9,2)
  );

insert into t values
 (1,1000,'2015-11-05',474.17,474.17)
,(2,1000,'2015-11-26',474.17,474.17)
,(3,1000,'2015-12-26',474.17,474.17)
,(4,1000,'2015-12-27',0,-474.17)
,(5,1000,'2016-01-26',474.17,0)
,(6,1000,'2016-02-26',474.17,0)
,(7,1000,'2016-03-26',474.17,474.17)
,(8,1000,'2016-04-26',474.17,0)
,(9,1000,'2016-05-26',474.17,0)
,(10,1000,'2016-06-26',474.17,474.17)
,(11,1000,'2016-07-26',474.17,0)
,(12,1000,'2016-08-26',474.17,1307.5)
,(13,1000,'2016-09-26',474.17,0)
,(14,1000,'2016-10-26',474.17,0)
,(15,1000,'2016-11-26',474.17,434.17)
,(16,1000,'2016-12-26',474.17,-434.17)
,(17,1000,'2017-01-26',474.17,0);

query:

declare @Date date = convert(date,getdate());

with cte as (
    select
        *
      , RunningAmountDue = sum(AmountDue)  over (order by date)
      , RunningAmountPaid= sum(AmountPaid) over (order by date)
                         + isnull(x.Reversal,0)
      , AccountBalance   = sum(AmountDue)  over (order by date)
                         - sum(AmountPaid) over (order by date)
    from t
      outer apply (
          select top 1
              Reversal = i.AmountPaid
            from t as i
              where i.AccountNumber = t.AccountNumber
                and i.RowNumber = t.RowNumber+1
                and i.AmountPaid < 0
        ) as x
    )

    select
        o.RowNumber
      , o.AccountNumber
      , Date                = convert(varchar(10),o.Date,120)
      , AmountDue           = convert(varchar(30),o.AmountDue,1)
      , AmountPaid          = convert(varchar(30),o.AmountPaid,1)
      , RunningAmountDue    = convert(varchar(30),o.RunningAmountDue,1)
      , RunningAmountPaid   = convert(varchar(30),o.RunningAmountPaid,1)
      , AccountBalance      = convert(varchar(30),o.AccountBalance,1)
      , PaidDate            = convert(varchar(10),x.PaidDate,120)
      , DaysOverDue         = datediff(day,o.Date,isnull(x.PaidDate,@Date))
    from cte o
      outer apply (
          select top 1
              PaidDate = i.date
            from cte i
            where i.AccountNumber=o.AccountNumber
              and i.RunningAmountPaid>=o.RunningAmountDue
            order by i.date asc
      ) as x;

results:

+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+
| RowNumber | AccountNumber |    Date    | AmountDue | AmountPaid | RunningAmountDue | RunningAmountPaid | AccountBalance |  PaidDate  | DaysOverDue |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+
|         1 |          1000 | 2015-11-05 | 474.17    | 474.17     | 474.17           | 474.17            | 0.00           | 2015-11-05 |           0 |
|         2 |          1000 | 2015-11-26 | 474.17    | 474.17     | 948.34           | 948.34            | 0.00           | 2015-11-26 |           0 |
|         3 |          1000 | 2015-12-26 | 474.17    | 474.17     | 1422.51          | 948.34            | 0.00           | 2016-03-26 |          91 |
|         4 |          1000 | 2015-12-27 | 0.00      | -474.17    | 1422.51          | 948.34            | 474.17         | 2016-03-26 |          90 |
|         5 |          1000 | 2016-01-26 | 474.17    | 0.00       | 1896.68          | 948.34            | 948.34         | 2016-06-26 |         152 |
|         6 |          1000 | 2016-02-26 | 474.17    | 0.00       | 2370.85          | 948.34            | 1422.51        | 2016-08-26 |         182 |
|         7 |          1000 | 2016-03-26 | 474.17    | 474.17     | 2845.02          | 1422.51           | 1422.51        | 2016-08-26 |         153 |
|         8 |          1000 | 2016-04-26 | 474.17    | 0.00       | 3319.19          | 1422.51           | 1896.68        | NULL       |         275 |
|         9 |          1000 | 2016-05-26 | 474.17    | 0.00       | 3793.36          | 1422.51           | 2370.85        | NULL       |         245 |
|        10 |          1000 | 2016-06-26 | 474.17    | 474.17     | 4267.53          | 1896.68           | 2370.85        | NULL       |         214 |
|        11 |          1000 | 2016-07-26 | 474.17    | 0.00       | 4741.70          | 1896.68           | 2845.02        | NULL       |         184 |
|        12 |          1000 | 2016-08-26 | 474.17    | 1307.50    | 5215.87          | 3204.18           | 2011.69        | NULL       |         153 |
|        13 |          1000 | 2016-09-26 | 474.17    | 0.00       | 5690.04          | 3204.18           | 2485.86        | NULL       |         122 |
|        14 |          1000 | 2016-10-26 | 474.17    | 0.00       | 6164.21          | 3204.18           | 2960.03        | NULL       |          92 |
|        15 |          1000 | 2016-11-26 | 474.17    | 434.17     | 6638.38          | 3204.18           | 3000.03        | NULL       |          61 |
|        16 |          1000 | 2016-12-26 | 474.17    | -434.17    | 7112.55          | 3204.18           | 3908.37        | NULL       |          31 |
|        17 |          1000 | 2017-01-26 | 474.17    | 0.00       | 7586.72          | 3204.18           | 4382.54        | NULL       |           0 |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+


If you're using largely innodb tables increasing the innodb_buffer_pool_size as large as possible (~80-90% system assuming nothing else but mysqld is really consuming ram).  This requires a mysqld restart though

Before the reload you can also set innodb_flush_log_at_trx_commit=2 to speed things up.  This disables flushing to disk every commit (breaking the D in ACID), but should be fine for a restore.  If there's a catastrophic failure during this process you're starting from scratch anyway.  Be sure to set this back to one after the restore.

This can be changed dynamically without a bounce

Ensure innodb_flush_method = O_DIRECT is set in your cnf.  This requires a restart

Is it getting pegged on CPU in top?  If you have a lot of compressed table data that will be inherently slower.  There's not much that can be done about this aside from getting a faster core.  Multi core wouldn't help you here since the load is done serially anyway.

If you're using spinning disks, if possible ensure the .sql source file is being read from a different set of HDDs.  Similarly, during the reload (unless you're trying to let the reload replicate) ensure binary logging and general logging are turned off.

If you're running 5.6 ensure performance schema is not enabled.


Seemingly simple query...excruciating response time

I would use LEFT JOIN with vt502 on the left side

SELECT t502.* FROM
(
    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING COUNT(PostalCode) > 1
) vt502 LEFT JOIN t502locations t502
ON vt502.PostalCode = t502.PostalCode;

or

SELECT t502.* FROM
(
    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING COUNT(PostalCode) > 1
) vt502 LEFT JOIN t502locations t502
USING (PostalCode);

Chances are, the number of PostalCodes with multiple records is probably so high that the MySQL (MariaDB) Query Optimizer does not like.

At the very least, I would run OPTIMIZE TABLE t502locations; and try these queries.


The LEFT() function in your example is not part of the EXEC statement. It is inside the string representing part of the dynamic SQL to be executed by your EXEC statement.

In contrast, the CAST() function is used to build that string. If you use the correct quotation marks – ' instead of ‘ & ’ – then perhaps you can also see the difference in the syntax highlighting:

DECLARE @NumberOfLetters int = 15;
EXEC('SELECT LEFT(Name,' + CAST(@NumberOfLetters AS varchar)
 + ') AS ShortName FROM Production.Product');

If syntax highlighting shows correctly on your monitor, then you should be able to see that the LEFT keyword is shown in red as part of a string, whereas the name CAST is shown in a different colour, and that is because it is not part of a string, rather it is part of an expression used to construct a string. That is precisely what the problem is: EXEC (...) does not accept an expression. It only accepts either a (single) string literal or a string variable.

Because you have to build your dynamic SQL script based on some values (the @NumberOfLetters value, in this case), you have to use a variable. You store the built script in it and then you pass it to the EXEC statement to run the dynamic script. That is what the modified code does:

DECLARE @NumberOfLetters AS int = 15;
DECLARE @str AS varchar(255);

-- you build the dynamic script first and store it in a variable
SET @str = 'SELECT LEFT(Name,' + CAST(@NumberOfLetters AS varchar)
         + ') AS ShortName FROM Production.Product';

-- then you just pass the variable to EXEC to run the dynamic script
EXEC(@str);


It depends on when the trigger fires during the transaction.

The trigger itself is part of the transactional context you are currently in. This means that any changes you have already made in the transaction so far will be visible to the trigger.

To take a simple example, consider this data model:

CREATE TABLE Foo (F INT)
CREATE TABLE Bar (B INT)
INSERT INTO Foo VALUeS (1)
INSERT INTO Foo VALUeS (2)
INSERT INTO Bar VALUeS (100)

CREATE TRIGGER Tri ON Foo
FOR UPDATE
AS
DECLARE @BarVal INT
SELECT @BarVal = B FROM Bar

In this case, if you modify Bar before Foo in the same transaction, then changes in Bar by your own transaction will be visible to Tri on Foo. Example:

BEGIN TRAN
  UPDATE Bar
  SET B = 200

  /* Prints 200 */
  UPDATE Foo
  SET F = F + 1
  ROLLBACK
BEGIN TRAN

And:

BEGIN TRAN
  /* Prints 100 */
  UPDATE Foo
  SET F = F + 1

  UPDATE Bar
  SET B = 200
ROLLBACK TRAN

Within the trigger code, you can see both the previous and the new value of the table the trigger fired on by using the special tables inserted and deleted documented here (old article, but still applies):

http://technet.microsoft.com/en-us/library/aa214435(v=sql.80).aspx

When you have triggers firing triggers, this very quickly becomes complicated. Generally, it is advisable to wrap complex logic operating on several tables in a stored procedure instead. That way you know that all the operations happen in the right order.


This sounds like a character set conversion issue to me, the clue being bytes rather than characters. Are you going from ASCII to a multibyte charset, e.g. Unicode? Check the target database, check your environment (NLS_ env vars), check the dumpfile itself (e.g. if you were using UTF8 in the source that's Unicode but not multibyte if you only used the lower code page).


How can I compare row values as column name to the another table and Get data (Sql Server-2017)?

SELECT t1.ID, t1.column_name, t1.column_value
FROM (
SELECT ID, 'PTA_1' column_name, PTA_1 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_2' column_name, PTA_2 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_3' column_name, PTA_3 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_4' column_name, PTA_4 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_5' column_name, PTA_5 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_1' column_name, DTA_1 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_2' column_name, DTA_2 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_3' column_name, DTA_3 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_4' column_name, DTA_4 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_5' column_name, DTA_5 column_value FROM Table1
) t1
JOIN (
SELECT ID, PartNum1 column_name FROM Table2 UNION ALL
SELECT ID, PartNum2 column_name FROM Table2 UNION ALL
SELECT ID, PartNum3 column_name FROM Table2 UNION ALL
SELECT ID, PartNum4 column_name FROM Table2 UNION ALL
SELECT ID, PartNum5 column_name FROM Table2 UNION ALL
SELECT ID, DataNum1 column_name FROM Table2 UNION ALL
SELECT ID, DataNum2 column_name FROM Table2 UNION ALL
SELECT ID, DataNum3 column_name FROM Table2 UNION ALL
SELECT ID, DataNum4 column_name FROM Table2 UNION ALL
SELECT ID, DataNum5 column_name FROM Table2
) t2 USING (ID, column_name);

fiddle
Move subqueries into CTEs if DBMS and its version allows.
Use UNPIVOT or similar instead of UNION ALL if DBMS and its version allows.

I'm using SQL server, Is it possible to use without mention the column names

WITH
cte1 AS ( SELECT ID, column_name, column_value
          FROM Table1
          UNPIVOT ( column_value FOR column_name IN ( PTA_1, PTA_2, PTA_3, PTA_4, PTA_5, DTA_1, DTA_2, DTA_3, DTA_4, DTA_5 )
                  ) AS unpvt ),
cte2 AS ( SELECT ID, column_value
          FROM Table2
          UNPIVOT ( column_value FOR column_name IN ( PartNum1, PartNum2, PartNum3, PartNum4, PartNum5, DataNum1, DataNum2, DataNum3, DataNum4, DataNum5 )
                  ) AS unpvt
          WHERE column_value > '' )
SELECT cte1.*
FROM cte1
JOIN cte2 ON cte1.ID = cte2.ID AND cte1.column_name = cte2.column_value;

fiddle


Claiming disk space after removing table field

We decided to delete that field and all its values: Is there a way to delete the ntext field and all its values and release space without removing indexing ,without shrinking, without loosing db performance?

I would recommend to use (from BOL : )
DBCC CLEANTABLE
(
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , batch_size ]
)
[ WITH NO_INFOMSGS ]

DBCC CLEANTABLE reclaims space after a variable-length column is dropped. A variable-length column can be one of the following data types: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. The command does not reclaim space after a fixed-length column is dropped.

!! CAUTION !! (use a careful batch size - its advisable to use this parameter if your table is massive):

DBCC CLEANTABLE runs as one or more transactions. If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows. DBCC CLEANTABLE cannot be run as a transaction inside another transaction.
This operation is fully logged.

A simple repro will prove that DBCC CLEANTABLE is better than SHRINKING (and no worry of fragmentation :-)
-- clean up
drop table dbo.Test

-- create test table with ntext column that we will drop later
create table dbo.Test (
    col1 int
    ,col2 char(25)
    ,col3 ntext
    );

-- insert  1000 rows of test data
declare @cnt int;

set @cnt = 0;

while @cnt < 1000
begin
    select @cnt = @cnt + 1;

    insert dbo.Test (
        col1
        ,col2
        ,col3
        )
    values (
        @cnt
        ,'This is a test row # ' + CAST(@cnt as varchar(10)) + 'A'
        ,REPLICATE('KIN', ROUND(RAND() * @cnt, 0))
        );
end

--drop the ntext column
ALTER TABLE dbo.Test DROP COLUMN col3 ;

--reclaim the space from the table
-- Note that my table is only having 1000 records, so I have not used a batch size
-- YMMV .. so find a maintenance window and you an appropriate batch size
-- TEST TEST and TEST before implementing in PROD.. so you know the outcome !!
DBCC CLEANTABLE('tempdb', 'dbo.Test') ;


SQL Server Management Studio 2014 - Show same .sql file in multiple tab groups

To my knowledge SQL Server Studio doesn't have a clone/compare feature. Having said that, it appears that SoftTree makes a product that integrates into SQL Server Studio allowing you to use Notepad++ as the editor (which looks very cool):

http://www.softtreetech.com/sqlassist/more_screenshots.htm

http://www.softtreetech.com/sqlassist/

Note: I have zero affiliation or investment with this company.

SoftTree looks to be a bit pricy. However, you can get a free plugin to run SQL code from Notepad++ here (I'm liking they way this looks and may have to try it myself):

https://github.com/gutkyu/NppDB


It looks like you are hitting this regression bug:

  select count(distinct N1), count(distinct N2) from test.AA" works incorrectly
  ...
  "This bug happens when a unique index exists"

One of the suggested workarounds is to use sql_buffer_result


Logical reads amount difference with almost identical indexes

It all depends on the definitions and the key (and non-key) columns defined in the nonclustered index.  The clustered index is the actual table data.  Therefore it contains all of the data in the data pages, whereas the nonclustered index is only containing columns' data as defined in the index creation DDL.

Let's set up a test scenario:

use testdb;
go

if exists (select 1 from sys.tables where name = 'TestTable')
begin
    drop table TestTable;
end
create table dbo.TestTable
(
    id int identity(1, 1) not null
        constraint PK_TestTable_Id primary key clustered,
    some_int int not null,
    some_string char(128) not null,
    some_bigint bigint not null
);
go

create unique index IX_TestTable_SomeInt
on dbo.TestTable(some_int);
go

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into dbo.TestTable(some_int, some_string, some_bigint)
    values(@i, 'hello', @i * 1000);

    set @i = @i + 1;
end

So we've got a table loaded with 1000 rows, and a clustered index (PK_TestTable_Id) and a nonclustered index (IX_TestTable_SomeInt).  As you've seen in your testing, but just for thoroughness:

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable -- with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- nonclustered index scan (IX_TestTable_SomeInt)
-- logical reads: 4

Here we have a nonclustered index scan on the IX_TestTable_SomeInt index.  We have 4 logical reads for this operation.  Now let's force the clustered index to be used.

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- clustered index scan (PK_TestTable_Id)
-- logical reads: 22

Here with the clustered index scan we have 22 logical reads.  Why?  Here's why.  It all matters on how many pages that SQL Server has to read in order to grab the entire result set.  Get the average row count per page:

select
    object_name(i.object_id) as object_name,
    i.name as index_name,
    i.type_desc,
    ips.page_count,
    ips.record_count,
    ips.record_count / ips.page_count as avg_rows_per_page
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TestTable'), null, null, 'detailed') ips
inner join sys.indexes i
on ips.object_id = i.object_id
and ips.index_id = i.index_id
where ips.index_level = 0;

Take a look at my result set of the above query:

As we can see here, there are an average of 50 rows per page on the leaf pages for the clustered index, and an average of 500 rows per page on the leaf pages for the nonclustered index.  Therefore, in order to satisfy the query more pages need to be read from the clustered index.


What is the purpose of the keyword “||” in PostgreSQL?

String concatenation as you can see here: http://www.postgresql.org/docs/9.4/static/functions-string.html

Array concatenation: http://www.postgresql.org/docs/9.4/static/functions-array.html

And others... It means: concatenation.


Implementing a function written for Postgres 9.4 in Postgres 8.4

The pre-format solution used to be the good old concatenation, made safe by using quote_ident().

In your case, it would look like

...
    EXECUTE $$
        CREATE TABLE IF NOT EXISTS $$ || quote_ident(rows.tablename) || $$ (
             date date,
             number integer
        ) $$;
...

Note that here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.

Another shortcoming of version 8.4 is that the CREATE TABLE IF NOT EXISTS syntax does not exist there - it was introduced with 9.1.  You can still do a test for it:

IF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relname = 'rows.tablename' AND nspname = 'your_schema')
THEN
    -- do the EXECUTE
END IF;

If you have only one schema around, you can omit the join to pg_namespace.  pg_class and pg_namespace are part of the system catalogs - you may want to check out what else can be found there.


How to Find Consecutive count of a column based on a their status

The problem in your query actually comes from the first row.
It has the same Reason_type so it gives the extra ranking to the bottom records.

I think you can use CONDITIONAL_CHANGE_EVENT, maybe like:

select *,
    dense_rank()
        over (partition by site,change_num,reason_type order by billing_date) as rank
from (
   Select site,
     billing_date,
     Reason_type,
     CONDITIONAL_CHANGE_EVENT(reason_type)
         over (partition by site order by billing_date) as change_num
   from AVG_SCN
   where site=3
) foo
order  by 1,2;


Finding differences between a master and slave when pt-table-sync fails

Here's a method that does not interrupt service on the master, though it does lock the slave temporarily.

Stop the slave.

mysql> STOP SLAVE;

Dump the offending table on the master with mysqldump in a repeatable-read transaction. Include the binary log coordinates in the output (--master-data). Output one INSERT per row (--skip-extended-insert).

$ mysqldump --single-transaction --master-data=2 --skip-extended-insert
  mydatabase myoffendingtable > master-dump.sql

Note the binlog coordinates in the dump file, and start slave until the coordinate shown in the master's dump file.

mysql> START SLAVE UNTIL MASTER_LOG_FILE='xxxx' MASTER_LOG_POS=yyyy;

Wait until the slave thread catches up to that position and stops itself. This shouldn't take long.

mysql> SELECT MASTER_POS_WAIT('xxxx', yyyy);

Dump the table on the slave, also with one row per INSERT.

$ mysqldump --skip-extended-insert mydatabase myoffendingtable > slave-dump.sql

Resume replication.

mysql> START SLAVE;

Now you can diff the dump files, and be assured they represent exactly the same logical point in the stream of changes. This allows you to find data discrepancies.


How to get space used by In-Memory table in SqlServer

I guess this helps you..

With the help of 'Cached Page count' we can calculate the Total space occupied.

        set nocount on;
        set transaction isolation level read uncommitted;
        select
           count(*)as cached_pages_count,
           (COUNT(*) * 8.0) / 1024 AS Total_MB_Occupied, -- convert pages into MB -  the page size is 8 KB for sql server
           obj.name as objectname,
           ind.name as indexname,
           obj.index_id as indexid
        from sys.dm_os_buffer_descriptors as bd
        inner join
        (
            select       object_id as objectid,
                               object_name(object_id) as name,
                               index_id,allocation_unit_id
            from sys.allocation_units as au
                inner join sys.partitions as p
                    on au.container_id = p.hobt_id
                        and (au.type = 1 or au.type = 3)
            union all
            select       object_id as objectid,
                               object_name(object_id) as name,
                               index_id,allocation_unit_id
            from sys.allocation_units as au
                inner join sys.partitions as p
                    on au.container_id = p.partition_id
                        and au.type = 2
        ) as obj
            on bd.allocation_unit_id = obj.allocation_unit_id
        left outer join sys.indexes ind
        on  obj.objectid = ind.object_id
        and  obj.index_id = ind.index_id
        where bd.database_id = db_id()
        and bd.page_type in ('data_page', 'index_page')
        and obj.name like '%TABEL NAME%'  --- **Provide your table name**
        group by obj.name, ind.name, obj.index_id
        order by cached_pages_count desc


Audit Admin rights use on SQL Server 2019

From the nature of the question, it sounds like you're observing the user discreetly before opting to revoke permissions. Definitely get an understanding of what the business is willing to allow the user to perform on the instance, especially if they aren't wearing the DBA hat in any capacity. If you're the DBA of the instance and you're responsible for its uptime, security and integrity, convey your security expectations with the business and ensure your users' logins are in compliance.
That being said, you may want to use a combination of Audits with both Server level and Database level specifications. The groups of actions that can be audited are vast but this will allow you the most precision if you want to ignore particular action groups. As for the indicating the principal (user/login), you can indicate the login you want to track in particular but it may be advisable to track the public role so you can monitor overall use of the high-level actions.

Another approach you can try is to start by looking at the default trace. It may not capture all events and its retention depends on disk space and the amount of qualifying events being generated but can be checked on the fly.
Both the above actions are passive with output checked periodically using SSMS and T-SQL. You can also interactively monitor with Extended Events sessions.


The problem:

  date store in varchar MM/DD/YY

That is your problem. You are storing dates in character columns. Even worse, in a non-aligned format.

Solution:

Use a type that suits your data. You are storing dates, use the DATE type.

Then you won't have to decide or worry about the internal format that dates are stored. You'll only have to provide date literals in a format that MySQL accepts (eg. ISO: YYYY-MM-DD). Your query would then work just fine:

SELECT *
FROM new_item
WHERE from_date >= '2017-01-01'
  AND   to_date <= '2018-02-25' ;


Triggers run, by default, under the security context of the principal who caused the trigger to fire.

In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause.

Below is an example which shows how that works.  WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, instead of the principal who is updating the table.

First, we create a test table:

USE tempdb;
IF OBJECT_ID('dbo.t') IS NOT NULL
BEGIN
    DROP TRIGGER t_trig;
    DROP TABLE dbo.t;
END
GO
CREATE TABLE dbo.t
(
    ID INT NOT NULL
    , ID2 INT NULL
);
GO

Here's the trigger code, with EXECUTE AS OWNER:

CREATE TRIGGER t_trig ON dbo.t
WITH EXECUTE AS OWNER
AFTER INSERT
AS
BEGIN
    UPDATE dbo.t
    SET ID2 = ID
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.ID = dbo.t.ID);
END
GO

Now, we'll create a test login with low privileges that can be used to test the hypothesis that EXECUTE AS OWNER allows an under-privileged principal access to functionality they otherwise would not have:

CREATE LOGIN tLogin WITH PASSWORD = 'QWERFsdf23454%';
CREATE USER tLogin FROM LOGIN tLogin WITH DEFAULT_SCHEMA = dbo;

We'll give them the ability to insert rows into dbo.T, but prevent them from running UPDATE statements:

GRANT INSERT ON dbo.t TO tLogin;
DENY UPDATE ON dbo.t TO tLogin;

Here we do the test:

EXECUTE AS USER = 'tLogin';
/*
    Output here shows we're running under the tLogin
    security context
*/
SELECT SUSER_SNAME();
/*
    This will fail, with insufficient privileges
    since we've DENY'd the UPDATE privilege to tLogin.
*/
UPDATE dbo.t SET ID2 = ID;
/*
    this will run the UPDATE since the trigger
    has EXECUTE AS OWNER
*/
INSERT INTO dbo.t(ID) VALUES (1);
/*
    This takes us out of the tLogin security context
*/
REVERT

Here we can see the row in dbo.T has the changes made by the trigger:

SELECT *
FROM dbo.t;

And here, we cleanup the low-privilege user:

DROP USER tLogin;
DROP LOGIN tLogin;

The output from running the above is:


Where to look for clues as to why SQL Server become unresponsive/crashed?

Only administrators may connect at this time.

Sounds like the UI has issued ALTER DATABASE ... SET RESTRICTED_USER and then hung. This hasn't actually hung SQL Server, it has only prevented non-admin users from connecting. You could get around this by making your application use sa - I'm kidding.

Honestly, stop using the UI for log backups - it's not very good at things like this, and it's hard to pick all the right options for success. BACKUP LOG ... TO DISK is much more reliable, transparent, error-free, and even source controllable...


Bit akward, I can't see how to comment but I wanted to ask why your condition isn't:

delete from ssn_sdo
where
       art_id=@art_id
       and skl_id=@skl_id
       and (@level = 0 or (@level = 1 and tip=@tip) or doc_id = @doc_id)

Not sure that helps at all, before or after you add any further indexes, but it'd interesting to see the executions plans (actual) and see if any benefit is realised.


Yes, that is my understanding as well, and this post helps provide some examples for you to review.

Plan guides (plan freezing) in SQL Server 2005/2008


The Zabbix tool, posted in the comment, is not really working, because the server doesn't run on Windows, only the agent. See info in their requirements page. This means you'd need to install the tool on a Linux machine and only an agent on the Windows machine. Great if you have a heterogeneous environment, not really helpful in a full Win environment.

The same situation is for Nagios, another interesting open source monitoring tool. They say there is a package only for Windows, but the doc page it redirects to doesn't work now.

Of complete free monitoring tools useful for SQL 2000 I'm not really aware, but you can use successfully a pair of:

Perfmon (find more info here)
Server trace (trace specific queries, not just everything) -> actually files that can be open in SQL Profiler
SQL Alerts (info in MSDN article or here, in this SimpleTalk article)

PS: found that a great monitoring tool is also working on SQL 2000. So you'd better try to test Confio Ignite free. It's fantastic for the price :-).

PS2: there is also a small free monitoring tool from Idera -> SQL Check, but I don't know if it works on SQL 2000.


How should I model a "communication preferences" field which may require different supporting info for each row?

It depends on how much you care. For instance, there is a totally standardized method of doing this with URI. That will support HTTP/HTTPS/Email, and even with maps physical addresses. You can usually store them in the very database with very little work..

scheme:[//[user[:password]@]host[:port]][/path][?query][#fragment]

You can see from that, a lot is optional. They only require

a scheme
a colon (:)
a host

There are lots of schemes specified. However some of your stuff is just goofy. It doesn't serialize requests, or address non-unique things like pigeons. What would that even look like? Globally addressable without a centralized authority (in this case a Global Pigeon Addressing Committee) is very difficult. You have to have a method of resolving conflicts (consensus).

Shy of that, you'll have to normalize the data and figure out what you really need to support. There is no database that is going to provide infinite abstractions without assumptions of use. You may as well just store your stuff in bytes.

Before you go down the EAV route, check to see if your database support
 binary JSON.


Postgres currently does not use parallelism when executing a single query. The entire execution engine is single threaded for each query (you can of course still execute multiple queries in parallel). This lack of "intra query parallelism" is one of the great drawbacks of running Postgres or MySQL. Basically, it makes those engines unfit for purpose on large datasets.

To get multi-threaded Postgres execution, you either need to split up the query into multiple queries yourself or run a Postgres compatible variant like Amazon Redshift or Greenplum (the former has added intra query parallelism to the code base, the latter does the splitting for you).

Whenever you feel the need to run a parallel query, first ask yourself: Could I add indexes that would make this fast enough without parallelism? If that answer is "Yes", pursue that option first. Very often though, you need brute force. And when it comes to brute force, vanilla Postgres is unfortunately not a particularly good database engine.


CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object

My first question would be do you have latest valid backup or not. Since database size is just 6G and also because minimum repair suggested by checkdb is repair_allow_data_loss , which can cause data loss and could also remove business constraints from database, it would be good to restore database from valid backup.

Before restoring the backup you need to check validity of the backup. You can do it using below

restore verifyonly from disk='Backup location'

If it comes out clean you can restore the backup to get back the database. The data loss also depends upon the recovery model of database if it is simple you can only restore full and differential backup if it is bulk logged and Full you can restore full ,differential and log backups to get minimum data loss.

Are you able to take backup of current database with continue after error. Please see if below command works, I guess it would

backup database db_name to disk='Location' with continue_after_error

This would generate corrupt backup of database now restore this backup file ON DIFFERENT SERVER using continue after clause normal restore wont be possible see TSQL Restore for restore command.

After above is done run checkdb within transaction like below on above restored database.

Begin transaction
dbcc checkdb('db_name', repair_allow_data_loss)
--commit

After this you can have look into database how much data checkdb has deleted to recover database. Please note checkdb also removes constraints so I would not ask you to run it. Please read Books Online Document  before proceeding

At last please see SQL Server errorlog and event viewer to see why database got corrupted


Looks like you need a GROUP BY.  Your query above done with a GROUP BY would look like this:

SELECT to_char(auditdate,'YYYY'), count(*)
FROM AuditLog GROUP BY to_char(auditdate,'YYYY');

A similar query can be used for other groupings.  For example, here would be a count for each day:

SELECT to_char(auditdate,'MM/DD/YYYY'), count(*)
FROM AuditLog GROUP BY to_char(auditdate,'MM/DD/YYYY');

To do this for each PageTitle, simply add PageTitle like this:

SELECT PageTitle , to_char(auditdate,'MM/DD/YYYY'), count(*)
FROM AuditLog GROUP BY PageTitle, to_char(auditdate,'MM/DD/YYYY');

To pivot the data as you have shown gets a bit more difficult particularly on 10g where there is no PIVOT. Here is one way to do it on 10g:

SELECT PageTitle
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/22/2012',1,NULL)) Oct_22
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/23/2012',1,NULL)) Oct_23
FROM AuditLog GROUP BY PageTitle;

Of course this requires entering all the dates.  You can make this a bit easier by generating the query using a query such as this:

SELECT 'SELECT PageTitle ' SQL FROM dual
UNION ALL
SELECT DISTINCT '    , Sum(DECODE(to_char(AuditDate,''MM/DD/YYYY''),'''
   || to_char(AuditDate,'MM/DD/YYYY') || ''',1,NULL)) ' || to_char(AuditDate,'Mon_DD')
FROM AuditLog
UNION ALL
SELECT 'FROM AuditLog GROUP BY PageTitle;' FROM dual;

Here is the sample data I used:

drop table auditlog;
create table AuditLog as (select sysdate auditdate, 'Page 1' PageTitle from dual);
insert into auditlog (select sysdate, 'Page 1' from dual);
insert into auditlog (select sysdate, 'Page 2' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 3' from dual);
commit;


Some of it will have to do with caching on the remote machine.  You've also got to account for what else is flying across the network.  Also some of those blocks may be in cache on the SQL Server's disk cache (not SQL's buffer pool) as they are the same exact blocks the disk may use the cached copy not the actual page you are restoring.

Don't forget things like witches, gremlins, boogie men, etc.


Why don't databases create their own indexes automatically?

Update

This is now implemented in SQL Server Azure. It generates recommendations

and index management can be configured to be automatic.

  Enable automatic index management

  You can set the SQL Database Advisor to implement recommendations
  automatically. As recommendations become available they will
  automatically be applied. As with all index operations managed by the
  service if the performance impact is negative the recommendation will
  be reverted.

Original Answer

Some databases do already (kind of) create indexes automatically.

In SQL Server the execution plan can sometimes include an Index Spool operator where the RDBMS dynamically creates an indexed copy of the data. However this spool is not a persistent part of the database kept in synch with the source data and it cannot be shared between query executions, meaning execution of such plans may end up repeatedly creating and dropping temporary indexes on the same data.

Perhaps in the future RDBMSs will have the capacity to dynamically drop and create persistent indexes according to workload.

The process of index optimisation is in the end just a cost benefit analysis. Whilst it is true that humans may have more information about relative importance of queries in a workload in principle there is no reason why this information could not be made available to the optimiser. SQL Server already has a resource governor that allows sessions to be classified into different workload groups with different resource allocations according to priority.

The missing index DMVs mentioned by Kenneth are not intended to be implemented blindly as they only consider the benefits to a specific query and make no attempt to take account of the cost of the potential index to other queries. Nor does it consolidate similar missing indexes. e.g. the output of this DMV may report missing indexes on A,B,C and A,B INCLUDE(C)

Some current issues with the idea are

The quality of any automated analysis that does not actually create the index will be highly dependant upon the accuracy of the costing model.
Even within the field of automated analysis an offline solution will be able to be more thorough than an online solution as it is imperative that an online solution should not add large book keeping overhead to the live server and interfere with its primary purpose of executing queries.
The indexes created automatically in response to workload will necessarily be created in response to  queries that would have found them useful so will lag behind solutions that create the indexes in advance.

It is probably reasonable to expect the accuracy of costing models to improve over time but point 2 looks trickier to solve and point 3 is inherently insoluble.

Nevertheless probably the vast majority of installs are not in this idealised situation with skilled staff who continuously monitor, diagnose, and anticipate (or at least react to)  changes in workloads.

The AutoAdmin project at Microsoft Research has been running since 1996

  The goal of this project is to make databases self-tuning and
  self-administering by exploiting knowledge of the workload

The project home page lists several intriguing projects. One is particularly relevant to the question here

  Another interesting problem arises when there is no DBA available
  (e.g. an embedded database or a small business). In such scenarios, a
  low touch continuous index tuning approach may become important. We
  have explored solutions ...[in]  “An Online Approach to Physical Design Tuning” in ICDE
  2007.

The authors state

  With increasingly common DBMS features like online indexes , it is
  appealing to explore more automatic solutions to the physical design
  problem that advance the state of the art.

The paper introduces an algorithm

  Its main characteristics are:

  As queries are optimized, we identify a relevant set of candidate indexes that would improve performance. This feature allows query
  processing to continue in parallel with indexes that are built in the
  background.
  At execution time, we track the potential beneﬁts that we
  lose by not having such candidate indexes and also the utility of
  existing indexes in the presence of queries, updates, and space
  constraints.
  After we gather enough “evidence” that a physical design change is beneﬁcial, we automatically trigger index creations or deletions.
  The online nature of our problem implies that we will generally lag behind optimal solutions that know the future. However, by carefully
  measuring evidence, we ensure that we do not suffer from “late”
  decisions signiﬁcantly, thus bounding the amount of incurred loss

The implementation of the algorithm allows for throttling in response to changes in server load and also can abort index creation if during creation the workload changes and expected benefit falls below the point that it is deemed worthwhile.

The conclusion of the authors on the topic of Online versus traditional physical tuning.

  The online algorithms in
  this work are useful when DBAs are uncertain about the future behavior
  of the workload, or have no possibility of doing a comprehensive
  analysis or modelling. If a DBA has full information about the
  workload characteristics, then a static analysis and deployment by
  existing tools (e.g., [2, 3]) would be a better alternative.

The conclusions here are similar to those in another paper Autonomous Query-driven Index Tuning

  Our approach cannot beat the index advisor if the whole workload is
  known in advance. However, in dynamic environments with evolving and
  changing workloads the query-driven approach produces better results.


Does teradata provides free or trial setup of multinode (at least 2 node) environment

Teradata Express is a single node 2 AMP only VM, but there's the free Teradata on VMware Developer Tier running on VMware ESXi which supports up to 2 nodes.

Or you could check Teradata on AWS & Azure, there's some free version, too, I don't know if they support multiple nodes.

But the main question is:

Why do you think you need that for testing? There's absolutely no difference if you got a single node or a multi node system.


How to store data in a database without tables?

All you need is persistence of your XML. Use a NoSQL solution or the file system.

There is no benefit in using an RDBMS, unless you want to use it instead of NoSQL or the file system.


An aggregate may not appear in the WHERE clause

AND we.UpdatedDate = (SELECT MAX(we.UpdatedDate)) is the offending statement.

Two easy ways to solve this are to get the Max(we.UpdateDate) in a variable first, or add an inner join (select max(we.UpdateDate) from WorkerExtension ) and joing that back to the "we" aliased table.


ERROR Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

Your network connectivity from slave to master was lost due t some reason and after the
Try the following:
STOP SLAVE;
START SLAVE;


Is there any benefit to defragmenting SQL indexes in a SAN environment?

Defragmentation strategies help improve scan speed to/from disk.

The wide variety of opinions is because an environment's ideal defragmentation strategy should depends on many different factors. There are also multiple potential layers of fragmentation in play.

Saying that your databases are stored on a SAN isn't enough information. For example:

Are database files stored on separate physical RAID groups or the same RAID group? What other processes are active on that same device? Are your backup files ending up there, too? You may have to ask your SAN admin for this information, because it's not always transparent.
What are the access patterns for the databases? OLTP is generally random access, but sometimes an application is table-scan-happy and you can't change its behaviour (ISV app). Are the applications read-mostly, write-mostly, or somewhere in between?
Are there performance SLAs in play during a recovery/failover period?

Brent's post assumes there is one giant pool of storage and everything shares it. This means the physical disks are rarely idle, and hence most access is random. If that is your situation, then the advice applies, and I agree with it for the most part. While this type of strategy is much easier to manage, it isn't necessarily (a) what you have in your environment, or (b) what is the best solution for your environment.

If index maintenance is burdensome, consider doing it less aggressively, and/or amortize the cost over the week (i.e., run light maintenance once/day, instead of heavy maintenance once/week).

You can also turn on the SortInTempdb option to potentially reduce the amount of logging that takes place in the user databases.


We have found the issue it was a Quorum disk resource attached to the AGCLUSTER which was not able to take into to the other nodes.

We removed the disk and issue is gone

Thank you everyone for your support and your valuable time!!


Test SQL Server connection with sqlcmd using machine name

You would generally not find an Application pool using the computer account to connect to SQL Server. At most I would not want to allow that type of configuration.

You should be able to accomplish it by running the process as the local system account,  NT AUTHORITY\SYSTEM. You can do this by using psexec. You would just specify -s parameter to execute it as the system account.

I think something like this should work:

psexec -s "sqlcmd -S myserver -E"


What to set innodb_buffer_pool and why..?

The biggest table you have makes up 16.47% (28/170) of the total data. Even if the table was highly written and highly read, not all 28G of the table is loaded in the buffer pool at one given moment. What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server.

Here is a more granular way to determine innodb_buffer_pool_size for a new DB Server given the dataset currently loaded in the current DB Server's InnoDB Buffer Pool.

Run the following on your current MySQL Instance (server you are migrating from)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

Run the formula IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

If IBPPctFull is 95% or more, you should set innodb_buffer_pool_size to 75% of the DB Server's RAM.

If IBPPctFull is less than 95%, run this formula : IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05.

SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;

The number for IBPSize (in GB) is the number that more closely fits your actual working dataset.

Now, if IBPSize is still too big for the biggest Amazon EC2 RAM Config, use 75% of the RAM for the Amazon EC2 DB Server.


When your cluster tries multiple times to attempt a failover and it maxes out on the failover threshold value, an error that you described occurs. Basically it gives up and goes into a failed/offline state and stops listening on Virtual Network Name. To find the RCA of your issue, you need to identify the errors that occurred prior to this error. Let's us know what you see and then we can further dig into issue.


SQL Server 2008 - Return records in chronological order, given a separation factor

Prior to SQL 2008, the most common solution was to use a UDF to calculate the great-circle distance between two points on a sphere. The Haversine formula is probably the most commonly used method.

Of course the Earth is not actually a perfect sphere, but this was considered "good enough" for most uses.

In SQL 2008, as you anticipated, such calculations are simplified and made more accurate by the introduction of the Geography and Geometry data types. Here's a brief sample of how you can use them to simplify distance calculations.

DECLARE @locations TABLE(locname VARCHAR(100), coord geography)
DECLARE @loc1 geography
DECLARE @loc2 geography

INSERT INTO @locations
VALUES('HOME', geography::Point(-81.810194, 41.478156, 4326))   --Note: Lat, Long, SRID
                                                                --The 4326 is the SRID (spatial reference id) used by SQL as
                                                                --a reference to the WGS 84 Standard. This is the same reference
                                                                --used by the GPS system
INSERT INTO @locations
VALUES('WORK', geography::Point(-81.687771, 41.498227, 4326))

SELECT * FROM @locations

SELECT @loc1 = coord FROM @locations WHERE locname = 'HOME'
SELECT @loc2 = coord FROM @locations WHERE locname = 'WORK'

SELECT @loc1.STDistance(@loc2) * 3.2808399  --STDistance is in meters so we multiply to convert to feet

SELECT @loc1.STIntersects(@loc2.STBuffer(300 / 3.2808399)) as isWithin300Ft  --This formula returns True when the point @loc1 intersects with the 300ft buffer zone around @loc2

The SRID is the key to the improved accuracy. The WGS 84 specification to which it refers includes a standardized coordinate system and a reference ellipsoid.  In other words, it accounts for the non-spherical nature of the Earth, giving better results than a pure spherical Great Circle calculation.

If GIS accuracy is important to your work, this is the simplest way to implement it in SQL 2008.


You are effectively asking PostgreSQL to scan the complete tables, since both of your WHERE conditions are not selective at all.

Since journ_notes contains some 30 million rows, that will always take some time.

PostgreSQL's estimates seem to be good, and the nested loop join is probably chosen because the other tables are either big enough to make a hash join unattractive, or work_mem is set too small to contain the resulting hash.

Try again with a large setting for work_mem and see if you get better results. Verify that PostgreSQL is doing the right thing by temporarily disabling nested loop joins with

SET enable_nestloop = off;

and checking if the execution becomes slower or not.

Other than that, perhaps parallel query can make it faster (though not cheaper), but you don't tell us your version.


Should an account be set up and used solely to install SQL Server?

There is no need or benefit to installing under a dedicated account as long as you are following other account-based best practices.


You are facing data corruption, and it seems that your database had crashed before and now the recovery process is failing.
If you cannot restore from a backup, you will have to find a professional who can salvage as much from the wreck as possible. A Q&A forum will not be able to guide you through this.
Apart from that, you should figure out what may have caused the data corruption, so that you can avoid it in the future. Often the cause is hardware problems.


How to speed up string cleanup function?

The function should be noticeably faster with fewer assignments (none, actually, in this updated version):

CREATE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
BEGIN
RETURN replace(replace(replace(replace(replace(replace(
         translate($1,'ŠŽšžŸÝÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜàáâãäåçèéêëìíîïñòóôõöøùúûüýÿ!"#$%&()*+,-./:;<=>?@[\]^_`{|}~€‚ƒ„…†‡ˆ‰‹‘’“”•–—˜™›¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×Þð÷þÐ'
                     ,'SZszYYAAAAAACEEEEIIIINOOOOOOUUUUaaaaaaceeeeiiiinoooooouuuuyy')
         ,'Œ','OE')
         ,'Æ','AE')
         ,'œ','oe')
         ,'æ','ae')
         ,'ß','ss')
         ,'''','');
END
$func$  LANGUAGE plpgsql;

Assignments are comparatively expensive in plpgsql.

But I suspect you really want to remove all accents (diacritic signs). Postgres provides the function unaccent() with the additional module unaccent:

CREATE OR REPLACE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
SELECT translate(unaccent(
        replace(replace(replace(replace(replace(
          $1
         ,'Œ','OE')
         ,'Æ','AE')
         ,'œ','oe')
         ,'æ','ae')
         ,'ß','ss')
       ), '!"#$%&()*+,-./:;<=>?@[\]^_`{|}~€‚ƒ„…†‡ˆ‰‹‘’“”•–—˜™›¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×Þð÷þÐ''', '');
$func$  LANGUAGE sql IMMUTABLE;

Substantially faster, yet. And superior in every aspect if you actually want to remove all diacritic signs.

In addition to unaccent():

Include an escaped ' ('') in translate() directly.
Make it a simple SQL function.
Make the function IMMUTABLE.

Detailed explanation and more related issues to consider if you need an IMMUTABLE function with unaccent():

Does PostgreSQL support “accent insensitive” collations?

In Postgres 9.5 or older we need to expand ligatures like 'Œ' or 'ß' manually, since unaccent() always substitutes a single letter:

    SELECT unaccent('Œ Æ œ æ ß');

    unaccent
    ----------
    E A e a S

What I would do in Postgres 9.6

After this update to unaccent:

  Extend contrib/unaccent's standard unaccent.rules file to handle all
  diacritics known to Unicode, and expand ligatures correctly (Thomas
  Munro, Léonard Benedetti)

Bold emphasis mine. Using unaccent() like instructed in the linked answer above:

CREATE OR REPLACE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
SELECT trim(regexp_replace(regexp_replace(public.unaccent('public.unaccent', $1)
                                        , '[^a-zA-Z\d\s]', '', 'g')
                         , '\s+', ' ', 'g'));
$func$  LANGUAGE sql IMMUTABLE;

Almost, but not quite 100 % identical to what you had. Better IMHO:

Replace all accents and expands all ligatures with unaccent().
Remove all noise characters (everything except ASCII letters, digits and white space).
Fold whitespace to a single space (' ').
Trim leading and trailing spaces.


Postgres won't use btree index in left-anchored LIKE query

Finally found this after digging through a bunch of tutorials that suggested I was already doing it the right way: http://www-old.bartlettpublishing.com/site/bartpub/blog/3/entry/329 . Relevant documentation is here: https://www.postgresql.org/docs/9.5/static/indexes-opclass.html

Looks like I'm using locale settings (that I didn't touch, just left default) that won't work for btree "like" index usage. I could have sworn that I wasn't seeing this problem before, and maybe I wasn't. I now have to do CREATE INDEX test_string ON test USING btree (string text_pattern_ops);


First, make sure you have set max_allowed_packet in the appropriate section [mysqldump] of the config file my.cnf and that it does not exceed the maximal value of 1G:

[mysqldump]
max_allowed_packet=1G

Or you can also specify it at runtime (without having to change my.cnf):

mysqldump --max-allowed-packet=1G ...


Restore user in DataBase that in master have mapped the child database but in child database doesn't have the user created

Your login DEFCON\axadmin is sysadmin on the current server.

This means that it cannot be mapped to any database as DEFCON\axadmin user, it's already mapped and it's mapped as dbo in any database.

To see this just impersonate this login and ask for a user in that database:

use DAXTest;
execute as login = 'DEFCON\axadmin';
select user;
-- you'll see 'dbo' here
select select IS_SRVROLEMEMBER('sysadmin');
-- you'll get 1 here
revert;


Can Mysql Cluster AQL(Push-Down Join) be trigger with sharding table at the same time?

Pushdown basically means that some part of the job is "pushed down" to data nodes. So performance gain is because:

data nodes can filter and process data in much more efficient way than SQL nodes. They "know" more about the physical details of the data
we save on SQL node - data node traffic. And latency between those two nodes is relatively high because of the MySQL Cluster design

Inter-data-node latency is much much lower. So data shipping does not have such impact on performance. And all the tables (except some absolute corner cases) are sharded between data nodes. For any data fetch data nodes must find which node holds active part of the required row.

This latency still cannot be ignored. One can find that for the best possible performance Dolphin Interconnect is suggested. But:

inter-data-node latency is lower
for one data fetch to SQL nodes several communication packets must pass between data nodes. And when filtering is done by data nodes much less packets are needed between data nodes for one processed row.


Looking for industry standard and/or tool to represent and export RDBMS metadata

If you want a set of views that are "common" across database vendors (Oracle, MySQL, PostgreSQL, ...), there are the INFORMATION_SCHEMA views.

They are supposed to be a common standard across vendors (exactly how true I don't know). In SQL Server (versions >= 2000) you access them via [INFORMATION_SCHEMA].{view_name}, where {view_name} is any one of:

CHECK_CONSTRAINTS,
COLUMN_DOMAIN_USAGE,
COLUMN_PRIVILEGES,
COLUMNS,
CONSTRAINT_COLUMN_USAGE,
CONSTRAINT_TABLE_USAGE,
DOMAIN_CONSTRAINTS,
DOMAINS,
KEY_COLUMN_USAGE,
PARAMETERS,
REFERENTIAL_CONSTRAINTS,
ROUTINE_COLUMNS,
ROUTINES,
SCHEMATA,
TABLE_CONSTRAINTS,
TABLE_PRIVILEGES,
TABLES,
VIEW_COLUMN_USAGE,
VIEW_TABLE_USAGE,
VIEWS.

Here's some tips on how to make use of them: INFORMATION_SCHEMA.TABLES | mssqltips.com.


Is it possible to backup with dump or rman from Oracle to MS Sql Server?

No, I don't think that this is possible. Both tools use Oracle specific file formats and can work only with Oracle databases. So the way to move data from an Oracle dump file created by the Oracle exp or expdp tool is to import the dump in an Oracle database and move it from this database to an SQL Server database.

Note 1: Toad from Quest is able to read Oracle export dumps and I cannot be 100% sure that there exists a tool that hacked the Oracle dump format to read the data and write it to SQL server.

Note 2: It is possible to migrate data from an Oracle database  to a Microsoft SQL Server database. Evidently Oracle ist not much interested in such migrations but Microsoft is, so you should search the Microsoft pages for tools, e.g. here is one for Oracle 12c and SQL Server 2017. HR is a sample schema in provided by Oracle. Oracle 9i is a rather old version, so I am not sure if this will work for Oracle 9i.

Note 3: I think it will make sense that you describe the whole problem more detailed. Why do you want to copy the data to your system, what are the requirements and constraints. What is the size of the data, what type of objects do you want to migrate,... Maybe it make sense to open a new question that describes the whole scenario. Maybe the question that you put here doesn't really help you to solve your original problem.


many Servers of SQL server 2012 needs mirroring

How can it automatically change host of one if the other one was dropped ?

From the client application side, your application connection string should use FailoverPartner pointing to secondary or mirrored server.

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
Initial Catalog=myDataBase;Integrated Security=True;

From database side, if you are running with a witness in high-safety mode (high-safety mode with automatic failover), then ONLY you will have flexibility of automatic failover, rest are manual failover.
Even though, mirroring allows you to do automatic failover in high-safety mode, make sure your keep monitoring the REDO queue as this can impact the recovery during a failover scenario.
FROM BOL :

Rolling forward the database involves applying whatever log records are currently in the redo queue on the mirror server.
The redo queue consists of the log records that have been written to disk on the mirror server but not yet rolled forward on the mirror database.
Failover time for the database depends on how fast the mirror server can roll forward the log in the redo queue, which, in turn, is determined primarily by the system hardware and the current work load. Potentially, a principal database can become so busy that the principal server ships log to the mirror server much faster than it can roll the log forward. In this situation, failover might take significant time while the mirror server rolls forward the log in the redo queue.
Is the performance of mirroring acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?

To monitor performance, you need to monitor and baseline your server instance using :

On the principal:

Log Bytes Sent/sec: Number of bytes of the log sent to the mirror per second.

Log Send Queue KB: Total kilobytes of the log that have not yet been sent to the mirror server.

Transaction Delay: Delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This counters reports the total delay for all the transactions in process at that time. To determine the average delay per transaction, divide this counter by the Transactions/sec counter. When running asynchronous mirroring this counter will always be 0.

Transactions/sec: The transaction throughput of the database. This counter is in the Databases performance object.

Log Bytes Flushed/sec: The rate at which log records are written to the disk. This is the log generation rate of the application. It plays a very important role in determining database mirroring performance. This counter is in the Databases performance object.

Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks.

On the mirror:

Redo Bytes/sec: Number of bytes of the transaction log applied on the mirror database per second.

Redo Queue KB: Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.

Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks on the mirror.

As a side note, you need to take care of migrating jobs, ssis packages, logins, etc once you failover to secondary or mirrored database.


How to restart the distributor agent of transactional replication?

I found a simple solution here.

Distribution Agent - Start /Stop in T-SQL
-- To STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db

--To START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db

My complete implementation, working on my test environment is below.
I have left everything there, select queries and procedures, to remind myself, where to go to get the data to be used as parameters to the sp_MSstartdistribution_agent and sp_MSstopdistribution_agent stored procedures.

--==================================================================================
-- script to start-stop the distribution agent job for a publication
-- marcelo miorelli
-- 12-mar-2015
--==================================================================================

--http://www.msqlserver.net/2013/01/distribution-agent-start-stop-in-t-sql.html

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test

use Bocss2
GO

-- get the distributor
sp_helpDistributor
--SQLDISTLON1 -- distributor server

--==================================================================================
-- Script to run on Distribution database
--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test

-- distributor database in this instance is called "distribution"
USE Distribution
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select * from dbo.MSrepl_errors
ORDER BY [TIME] DESC

-- Get the publication name based on article
SELECT DISTINCT
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, da.name AS distribution_agent_job_name
FROM MSArticles a
JOIN MSpublications p ON a.publication_id = p.publication_id
JOIN MSsubscriptions s ON p.publication_id = s.publication_id
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
JOIN master..sysservers srv ON srv.srvid = p.publisher_id
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
     AND da.subscriber_id = s.subscriber_id
--WHERE da.name = 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'
ORDER BY 1,2,3

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test

-- Run from Publisher Database
-- Get information for all databases
DECLARE @Detail CHAR(1)
SET @Detail = 'Y'

BEGIN TRY
DROP TABLE #tmp_replcationInfo
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #tmp_replcationInfo (
PublisherDB VARCHAR(128),
PublisherName VARCHAR(128),
TableName VARCHAR(128),
SubscriberServerName VARCHAR(128),
)
EXEC sp_msforeachdb
'use ?;
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1
insert into #tmp_replcationInfo
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
'
IF @Detail = 'Y'
   SELECT * FROM #tmp_replcationInfo
ELSE
SELECT DISTINCT
PublisherDB
,PublisherName
,SubscriberServerName
FROM #tmp_replcationInfo

-------------------------------------------------------------------------
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
,SP.*
,SA.*
,S.*
,SRV.*

from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
-------------------------------------------------------------------------

--==================================================================================

--Msg 21482, Level 16, State 1, Procedure sp_MSstopdistribution_agent, Line 22
--sp_MSstopdistribution_agent can only be executed in the "distribution" database.

--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test

 --Distribution Agent - Start /Stop in T-SQL
    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
     @subscriber, @subscriber_db

--Msg 21846, Level 16, State 1, Procedure sp_MSdistributionagentjobcontrol, Line 60
--Cannot find a distribution agent job for the specified transactional or snapshot push subscription.

use distribution
go

sp_helpdistpublisher

select msda.*
       ,sjv.*
       ,sjs.*
      from dbo.MSdistribution_agents as msda
inner join msdb.dbo.sysjobs_view as sjv
        on msda.job_id = sjv.job_id
inner join msdb.dbo.sysjobsteps as sjs
        on sjv.job_id = sjs.job_id

    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent 'SQLBOCSSLON1', 'Bocss2Archive', 'Bocss2ArchiveMergedAccounts',
     'REPLON1', 'Bocss2Archive'

    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'

    --To START the Distribution Agent:
    sp_MSstartdistribution_agent @publisher = 'SQLBOCSSLON1',
                                 @publisher_db = 'Bocss2Archive',
                                 @publication = 'Bocss2ArchiveMergedAccounts',
                                 @subscriber = 'REPLON1',
                                 @subscriber_db = 'Bocss2Archive'

    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'


Setting up MySQL circular replication in existing replication topology?

STEP01) Add this to /etc/my.cnf on ServerB

[mysqld]
log-bin=mysql-bin

STEP02) Restart mysql on ServerB without replication running

service mysql restart --skip-slave-start

STEP03) On ServerB, run this in mysql client

SHOW MASTER STATUS;

You should see something like this:

+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 590591464 |              |                  |
+------------------+-----------+--------------+------------------+

STEP04) Record the Log File and Position from STEP03

STEP05) Restart mysql on ServerB as normal

service mysql restart

STEP06) Setup Replication User on ServerB

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ...

STEP07) On ServerA, run the following

CHANGE MASTER TO
MASTER_HOST='IP Address of ServerB',
MASTER_PORT=3306,
MASTER_USER='replication username',
MASTER_PASSWORD='replication password',
MASTER_LOG_FILE='File From STEP04',
MASTER_LOG_POS=Position From STEP04;

Suppose ServerB's IP address is 10.1.2.30, replication user/pass is 'repluser'/'replpass'

CHANGE MASTER TO
MASTER_HOST='10.1.2.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=590591464;

STEP08) Start Replication on ServerA by running

START SLAVE;

STEP09) Run SHOW SLAVE STATUS\G on ServerA

It should look something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.89.147
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000109
          Read_Master_Log_Pos: 636515875
               Relay_Log_File: relay-bin.000038
                Relay_Log_Pos: 636509693
        Relay_Master_Log_File: mysql-bin.000109
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 636515875
              Relay_Log_Space: 636509885
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.01 sec)

If Slave_IO_Running and Slave_SQL_Running both say Yes, CONGRATULATIONS You've done it !!!

Give it a Try !!!


Have several rows, or one single row with a huge comma separated data?

Use 1 row per ID, add an index on the ID. SQL is pretty good about finding a single value in a table, even if there are millions of rows. If you run periodic cleanup to wipe the old ID values out (like maybe have an automatic timestamp column and then have a job to drop all values older than X days), you wont run into any performance issues.

To give you an idea, in the production environment I work with, we have a similar table, about 40 columns wide, with some instances having >50 billion records, and pulling a single record out, queried by its unique, indexed ID, takes around 0.5s. Its very good hardware but its still pretty performant.


MySQL - Duplicate Entry when trying to install a Plugin

In the end, I renamed the entry in mysql.plugin, and then I was able to install it. Once I checked it was OK I then removed the entry in mysql.plugin.

So far it seems to be working fine,


MySQL Replication Duplicate Entry for Primary Key

What the number 914166 indicates?

The number indicates the row of the table where you are trying to insert data. For example, you are trying to insert some values to 914166th row.

Why this error occurs?

This error occurs because of duplicate value. You are trying to insert/update 914166 to a table activity as a primary key, but the value 914166 already exists in that table activity.

How do you actually continue with the query that poses the error?

You cannot continue with that query, since primary key column is a unique column, it does not accept duplicate values. You can skip this query and continue with the next query by using the following statements:

stop slave;

set global sql_slave_skip_counter = 1;

start slave;

Please note that, skipping errors is not a good option, you should try to fix the issues and start slave as suggested by MySQL.

Work around:

Is that mentioned query is complete statement?

It seems the given query is not a insert query but it is a update statement and it is not updating primary key of the activity table.

Check in master server, whether the mentioned primary key (id) column belongs to activity table or not and check the value (id=914166) exists in that column.

select * from zo_dev_20121216.activity where id = 914166;

If the value exist in that activity table, check in slave server, whether the particular value (id=914166) is exist or not, if it exist in slave server, you can simply skip the query.

select * from zo_dev_20121216.activity where id = 914166;

If you are getting empty result set for the above 2 queries, you can be sure that, the query is incomplete, or you are debugging wrong query.

The point is that, the query is trying to insert/update a value into a activity table which is already exists in that table.

I hope it will help you as a starting point for debugging the error.