pt-query-digest results are empty I have a slow log generated by MySQL 5.1.71-log, that I've copied onto another machine. It appears I'm not getting any results from pt-query-digest.

$> pt-query-digest slow.log

# 2.1s user time, 910ms system time, 506.36M rss, 687.04M vsz
# Current date: Fri Sep 12 10:39:50 2014
# Hostname: util.ultralinq.net
# Files: slow.log
# Overall: 0 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query size        66.26M  66.26M  66.26M  66.26M  66.26M       0  66.26M

# Profile
# Rank        Query ID    Response time Calls       R/Call      V/M   Item
# =========== =========== =========== =========== =========== ===== ======

$> head -n 1 slow.log
"2014-07-10 17:02:48","XXX[XXX] @  [A.B.C.D.]","00:00:18","00:00:00",4,1817425,"echo",0,0,3,"(SELECT 'readCount' AS var, COUNT(id) AS data FROM study\n                WHERE ctime > '2014-07-09 21:02:29' AND status='read') union (SELECT 'unreadCount' AS var, COUNT(id) AS data FROM study\n                WHERE ctime > '2014-07-09 21:02:29' AND status in ('unread', 'upload')) union (SELECT 'avgStudySize' AS var, SUM(size)/COUNT(distinct study_id) AS data FROM echo\n                WHERE ctime > '2014-07-09 21:02:29') union (SELECT 'procDelay' AS var, TIME_TO_SEC(TIMEDIFF('2014-07-09 21:02:29', MIN(ctime))) AS data FROM echo\n                WHERE ctime > '2014-07-09 21:02:29' and post_process_status = 'unprocessed')"

$> wc -l slow.log
144973 slow.log

$> pt-query-digest --version
pt-query-digest 2.2.10

Why am I not getting more interesting results?

Alan, the format you are showing does not seem to correspond with one of the compatible ones expected by pt-query-digest.

This one is the one expected:

$ cat slow.log
/usr/sbin/mysqld, Version: 5.6.20-68.0-log (Percona Server (GPL), Release 68.0, Revision 656). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 140912 16:10:36
# User@Host: root[root] @ localhost []  Id: 116621
# Schema:   Last_errno: 0  Killed: 0
# Query_time: 11.000250  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 64
SET timestamp=1410538236;
SELECT sleep(11);

Yours looks like the CSV export of the slow query log as written into table (not file) -log_output = TABLE:

mysql> desc slow_log;
+----------------+---------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                | Null | Key | Default           | Extra                       |
+----------------+---------------------+------+-----+-------------------+-----------------------------+
| start_time     | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host      | mediumtext          | NO   |     | NULL              |                             |
| query_time     | time                | NO   |     | NULL              |                             |
| lock_time      | time                | NO   |     | NULL              |                             |
| rows_sent      | int(11)             | NO   |     | NULL              |                             |
| rows_examined  | int(11)             | NO   |     | NULL              |                             |
| db             | varchar(512)        | NO   |     | NULL              |                             |
| last_insert_id | int(11)             | NO   |     | NULL              |                             |
| insert_id      | int(11)             | NO   |     | NULL              |                             |
| server_id      | int(10) unsigned    | NO   |     | NULL              |                             |
| sql_text       | mediumtext          | NO   |     | NULL              |                             |
| thread_id      | bigint(21) unsigned | NO   |     | NULL              |                             |
+----------------+---------------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.00 sec)

I may be wrong, but as far as I know, that format is not available to be parsed (the only formats available are: slowlog, binlog, genlog, rawlog and tcpdump).

However, it is feasible to transform your format to the slow log format, as all the info is there.

In the future, You may want to export the table doing the conversion indicated here:

mysql -u user -p -h host.rds.amazonaws.com -D mysql -s -r -e "SELECT CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time),  '  Lock_time: ', TIME_TO_SEC(lock_time), '  Rows_sent: ', rows_sent, '  Rows_examined: ', rows_examined, '\n', sql_text, ';' ) FROM mysql.slow_log" > /tmp/mysql.slow_log.log

(This is because I assume that you are logging into a table because you are using RDS. if not, just use the log_output = FILE option)


SELECT all substrings (n-grams) of length n? How could I select all n-grams, ie. substrings of length n from a string using SQL? For example, the 3-grams of string example are exa, xam, amp, mpl, ple.

I'm using PostgreSQL to be more precise.

This does the trick:

SELECT SUBSTRING('example' FROM n FOR 3)
FROM GENERATE_SERIES(1, LENGTH( 'example' )-2, 1) n;
exa
xam
amp
mpl
ple

Here it is in a function:

CREATE OR REPLACE FUNCTION ngrams(varchar, integer) RETURNS SETOF TEXT AS $$
SELECT SUBSTRING($1 FROM n FOR $2)::TEXT
FROM GENERATE_SERIES(1, LENGTH($1)-($2-1), 1) n;
$$ LANGUAGE SQL;

Use it like:

SELECT ngrams('example', 3)


Cardinality Estimate for LIKE operator (Local Variables) I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses).

When executing the below query against the credit database I get different estimates under the different CEs.  Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived.  Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser.  I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I couldn't find anything online other than this Itzik Ben-Gan article, which states "When using the LIKE predicate in all optimize for unknown scenarios both the legacy and new CEs use a 9 percent estimate.". The information in that post would appear to be incorrect.

The guess for LIKE in your case is based on:

G: The standard 9% guess (sqllang!x_Selectivity_Like)
M: A factor of 6 (magic number)
D: Average data length in bytes (from statistics), rounded down to integer

Specifically, sqllang!CCardUtilSQL7::ProbLikeGuess uses:

  Selectivity (S) = G / M * LOG(D)

Notes:

The LOG(D) term is omitted if D is between 1 and 2.
If D is less than 1 (including for missing or NULL statistics):
D = FLOOR(0.5 * maximum column byte length)

This sort of quirkiness and complexity is quite typical of the original CE.

In the question example, the average length is 5 (5.6154 from DBCC SHOW_STATISTICS rounded down):

Estimate = 10,000 * (0.09 / 6 * LOG(5)) = 241.416

Other example values:

 D  = Estimate using formula for S
 15 = 406.208
 14 = 395.859
 13 = 384.742
 12 = 372.736
 11 = 359.684
 10 = 345.388
 09 = 329.584
 08 = 311.916
 07 = 291.887
 06 = 268.764
 05 = 241.416
 04 = 207.944
 03 = 164.792
 02 = 150.000 (LOG not used)
 01 = 150.000 (LOG not used)
 00 = 291.887 (LOG 7) /* FLOOR(0.5 * 15) [15 since lastname is varchar(15)] */

Test rig

DECLARE
    @CharLength integer = 5, -- Set length here
    @Counter integer = 1;

CREATE TABLE #T (c1 varchar(15) NULL);

-- Add 10,000 rows
SET NOCOUNT ON;
SET STATISTICS XML OFF;

BEGIN TRANSACTION;
WHILE @Counter <= 10000
BEGIN
    INSERT #T (c1) VALUES (REPLICATE('X', @CharLength));
    SET @Counter = @Counter + 1;
END;
COMMIT TRANSACTION;

SET NOCOUNT OFF;
SET STATISTICS XML ON;

-- Test query
DECLARE @Like varchar(15);
SELECT * FROM #T AS T
WHERE T.c1 LIKE @Like;

DROP TABLE #T;


What are the prime and non-prime attributes in this relation? R(HIJKLMNO)

L -> MNO
HI -> JKLMNO
J -> KL
K -> H

HI is a key.

So, H is a prime attribute.

But, H is also in non-key part.

What are the prime and non-prime attributes in this relation?

Your schema has three (candidate) keys:

HI
IJ
IK

While it is immediate to discover tha HI is a candidate key since it determines all the other attributes, you can see that this is true also for IJ and IK by calculating the closure of those attributes:

IJ+ = IJ
IJ+ = IJKL     (by adding the right part of J → KL)
IJ+ = IJKLH    (by adding the rigth part of K → H)
IJ+ = IJKLHMNO (by adding the right part of L → MNO)

analogously for IK:

IK+ = IK
IK+ = IKH      (by adding the rigth part of K → H)
IK+ = IKHJLMNO (by adding the right part of HI → JKLMNO)

For this reason, the prime attributes of the relation are:

HIJK

while the non prime attributes are:

LMNO

Note that there are no other candidate keys since M, N and O appear only on the right side of functional dependencies, so that they cannot “contribute” to any key. L appears both on a left part and on a right part of functional dependencies, but it is determined by HI, IJ and IK, and does not determine any of these attributes, so it cannot be part of a key. Finally, you cannot remove any attribute from HI, IJ and IK without losing the key property.


Index not being used on array elements in JSONB column I have a table testuser like this:
CREATE TABLE testuser (id int, data jsonb);

where the following snippet shows the format of the data column:
INSERT INTO testuser (id, data) VALUES (1, '{
  "name": "peter",
  "permissions": [
    {
      "type": "ARTICLE",
      "aggregateReference": "ae8b9fed-f99b-498e-b9ab-b87b1eec94a7"
    }
  ]
}'::jsonb);

Now, I've created a gin index on the permissions field like this:
CREATE INDEX test_user_permission_ix ON testuser USING gin((data -> 'permissions'));

but when I try to generate some additional rows, and run a query, the index is not being used:
SET enable_seqscan=false;
INSERT INTO testuser SELECT * FROM generate_series(1,100);
EXPLAIN ANALYZE SELECT * FROM testuser WHERE data @> '{"permissions" : [{"type":"ARTICLE"}]}';
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Seq Scan on testuser  (cost=10000000000.00..10000000170.01 rows=10 width=141) (actual time=26.040..26.453 rows=1 loops=1)
   Filter: (data @> '{"permissions": [{"type": "ARTICLE"}]}'::jsonb)
   Rows Removed by Filter: 10000
 Planning Time: 0.051 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.327 ms, Inlining 3.541 ms, Optimization 16.740 ms, Emission 5.626 ms, Total 26.233 ms
 Execution Time: 26.861 ms
(9 rows)

I can see in the pg_indexes table that the type of the permissions field seem to be ::text, but I'm not sure if this affects anything:
 schemaname | tablename |        indexname        | tablespace |                                              indexdef
------------+-----------+-------------------------+------------+-----------------------------------------------------------------------------------------------------
 public     | testuser  | test_user_permission_ix |            | CREATE INDEX test_user_permission_ix ON public.testuser USING gin (((data -> 'permissions'::text)))

Any suggestions on why the index is not being used would be very appreciated!

The left hand side of the WHERE expression must match the expression in the index, in order for the index to be usable (because the index stores a copy of that expression's value).
As your index is created on the expression data -> 'permissions' you need to use that in the query:
select *
from testuser
where data -> 'permissions' @> '[{"type":"ARTICLE"}]';

Note that this doesn't guarantee that the index is used. If the condition returns a substantial part of the table, the Seq Scan is still faster.

For the condition data @> '{"permissions": [{"type": "ARTICLE"}]}' you would need to create an index on data (not an expression)


DROP DATABASE statement cannot be used inside a user transaction Not really sure if this question belongs here, but I hope someone could help me out.

I've made integration tests going all the way down to the database (using mssql localDB). I want each test to run independently with it's own data - I want to reseed the database with my fake data before each test is running. I tried to implement it with transactions without success. Here is how I tried to pull it off:

public class TestDbInitializer : DropCreateAlways<MyContext>()
{
    public static List<Item> Items;

    public override Seed(DbContext context)
    {
        Items = new List<Item>();

        // Adding items
        // ..

        Items.ForEach(x => context.Add(x));

        context.SaveChanges();
    }
}

public class BaseTransactionsTests
{
    private TransactionScope _scope

    [TestInitialize]
    public void Initialize()
    {
        _scope = new TransactionScope();
    }

    [TestCleanup]
    public void Cleanup()
    {
        _scope.Dispose();
    }
}

[TestClass]
public class IntegrationTests : BaseTransactionsTests

private IDependenciesContainer _container;

public static void AssemblyInit(TestContext context)
{
    Database.SetInitializer(new TestDbInitializer());

    _container = new DependenciesContainer();

    // Registers all my application's dependencies
    _container.RegisterAll();
}

[TestInitialize]
public void Initialize()
{
    using (var context = new MyContext("TestsDatabase"))
    {
        context.Initialize(true);
    }
}

[TestMethod]
public void TestAddItem()
{
    var controller = _container.Resolve<MyController>();

    var result = controller.AddItem(new Item({Name = "Test"}))

    var goodResult = result as OkNegotiatedResult<string>();

    if (result == null)
        Assert.Fail("Bad result")

    using (var context = new MyContext("TestsDatabase"))
    {
        Assert.AreEqual(context.Items.Count, TestDbInitializer.Items.Count + 1)
    }
}

I use my dependency injector in my tests, registering all dependencies once (AssemblyInitialize).

I created a DB instance for testings, and a specific DropCreateAlways initializer with a fake data Seed method, which I set as the initializer in the AssemblyInitialize as well.

I want to reseed the database with my fake data before each test run. For that case I implemented the base class which holds a transaction scope.

When I run my tests, the following exception is thrown when Seeding the database in the TestInitialize:

DROP DATABASE statement cannot be used inside a user transaction

How should I deal with it? Moreover, what do you think of my implementation of those integration tests? What could be improved?

Create a separate initializer that drops the database if it exists, then creates the database outside of any transaction handling code.  If this fails for any reason, you could (probably) just fail the test right then and investigate why it failed.

From MSDN:

  The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

If you want, you could do this for each individual test:

DROP DATABASE if it exists
CREATE DATABASE
 Create necessary DDL
 Run DML unit test
DROP DATABASE

However, that would be overkill.  I'd simply create the database at the start of all tests, and drop it at the end.  Each unit test could be wrapped in a transaction which is either rolled-back or committed, depending on the requirements of further tests.

I looked for a resource that explains why CREATE DATABASE cannot be used inside a transaction, but I couldn't find one. Kenneth Fisher provided the following TechNet link, which shows all commands that won't work in a transaction:

Transact-SQL Statements Allowed in Transactions

Our guess is that a lot of them aren't allowed because they affect the file system, which is not transactional.


Why query cache plan kept dynamic queries but not sp? My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our Stored Procedures frequently got performance spikes.

I looked into it and seems the plan cache periodically clears stored procedure plans, but keeps lots of dynamic SQL statements in the plan cache.

I am bit confused on why SQL Server (SQL Server 2017) is doing this. How does SQL Server decide which one to remove?

Cached Plans are typically only removed from the plan cache under memory pressure.

SQL Server primarily considers the cost of the plan when deciding which plans to remove.  Lost-cost plans are removed before high-cost plans.  The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.

When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%.  For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused.  If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted.  For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost.  You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.

Cache cost is measured in units known as "ticks", with a maximum of 31.  Ticks are incremented according to this:

each I/O adds 1 tick, up to a maximum of 19 ticks.
each context switch adds one tick, up to a maximum of 8 ticks.
one tick per 16 pages of memory, with a maximum of 4 ticks.

Ticks start to be decremented once the plan cache reaches 50% of its capacity.  At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.

The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.

The details listed above are based on details found in SQL Server Internals by Kalen Delaney.


Galera Cluster not syncing from a standalone Mysql Server I had setup a 3 node Galera cluster in production network. And I am replicating the data from a Standalone MySQL Server to one of the galera node(node1) through master slave replication.

I can replicate data within the cluster. But the problem is the data replicated from the Standalone server to the node1 is not replicating across the node2 and node3. I can see the exact async replica of MySQL standalone server in node1.

Thanks in advance.

 -----(Master)        -----
 MySQL --- >          Node1(Slave)
 -----                -----
                   /         \
                  |           |
                -----        -----
                Node2        Node3
                -----        -----

The question comment guess by jerichorivera was absolutely right:

  log-slaves-updates on node1 enabled?

..but you seem to have misunderstood.

log_bin=/var/log/mysql/mysql-bin

(or whatever file basename) to enable writing the binary logs a slave can read from. However, this alone lets the node write empty binary logs (a size of a few hundred bytes) if it is slave on its own. To act as intermediate master, also set

log_slave_updates

in the my.cnf.


SQL Server 2008 - performing nightly full backups & transaction log shipping, but log file keeps growing Rather perplexed with my SQL server Log file. With identical primary & secondary servers, the production server on live sends the log files to the secondary every 15 minutes.
Also, every evening, a full backup (using the maintenance plan wizard) performs a full backup to a file location on the secondary server.

However, the log file seems to continually steadily grow. The database is 43Gb in size, and the log file has steadily increased to nearly 81Gb now. There are only about 80 users using the database.

On top of the full backup, I added a transaction log backup just to see if this made a difference, but still the log file is expanding. There's a weekly re-indexing job that runs on a Saturday night.

Database is on SQL Server 2008R2 (was migrated from a 2005 server a few months ago). It's in 2008 compatibility mode now, Full recovery model.

Any help greatly appreciated!

On top of the full backup, I added a transaction log backup just to see if this made a difference

This is a big no no.  By doing a manual transaction log backup, you can break the LSN of the log shipping secondary and cause the restores to fail, unless you used the copy_only option, which doesnt allow for truncation anyway.

There are only a few things that can cause a log not to truncate/clear, an active transaction, the recovery model set to full w/ no t-log backups, or a feature that is not working properly (mirroring, replication etc...)

You can check for open transactions by running DBCC OPENTRAN in the context of the said database.  Also, I know it may seem like a waste, but I would also confirm that your log backups are actually succeeding.  It could be a situation where the backup drive is out of space and the backups are failing.

Edit:
I forgot to add, when your log does truncate, you will want to shrink your log file as the number of VLFs will probably quite large and could degrade performance.  You can use dbcc shrinkfile


SQL Server collecting SQL statements using particular index I'd like to capture SQL statements that uses a particular index. Is there a way I can utilize SQL Profiler or extended events to do so? I know I can view the plan cache to get a gist of the queries, but I'd like to capture the currently being executed queries real time.

the following query will find all the queries that are using that particular index at realtime

  select  i.object_id,
     i.index_id, i.name,
     tqp.query_plan,pl.plan_handle, pl.start_time,pl.estimated_completion_time,q.text
  from sys.indexes as i
    cross apply (select quotename(i.name) as name) as i2
    cross apply sys.dm_exec_requests as pl
    cross apply sys.dm_exec_query_plan(pl.plan_handle) AS p
    CROSS APPLY sys.dm_exec_text_query_plan(pl.plan_handle, DEFAULT, DEFAULT) tqp
    cross apply sys.dm_exec_sql_text(pl.plan_handle) q
    where i.name = 'YourIndex'


How to select info about indexes with specified columns from all databases in the instance using TSQL? This query selects all the indexes - its names, fragmentation, and some other parameters from the current database.

select
    idx.[name] as [index_name],
    sc.[name] as [schema_name],
    obj.[name] as [table_name],
    ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,'LIMITED') AS ips
where idx.[name] is not NULL
order by [fragmentation_percent] desc;

Now I need to select all the indexes with specified columns from all databases. I can use USE [dbname] when I execute the statement manually, but I need to put it in a cursor, so I cannot write USE [dbname] in a cursor.

How can I substitute the database selection in the ...CROSS APPLY sys.dm_db_index_physical_stats( DB_ID(), ... part of my statement?

Help will be much appreciated to show how can I select all database indexes with a single query.

Something like this:

declare cr_index cursor
for
exec(@dynamicUSE)
select
    idx.[name] as [index_name],
    sc.[name] as [schema_name],
    obj.[name] as [table_name],
    ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,'LIMITED') AS ips
where idx.[name] is not NULL
order by [fragmentation_percent] desc;

You can do dynamic SQL in a cursor to do it on every database on the server that way :

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(1000)
DECLARE database_cursor CURSOR FOR
SELECT name FROM MASTER.sys.sysdatabases

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @Command = 'USE [' +  @DB_Name + '];  select
   idx.[name] as [index_name],
   sc.[name] as [schema_name],
   obj.[name] as [table_name],
   ips.[avg_fragmentation_in_percent] as [fragmentation_percent]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sc  on obj.schema_id = sc.schema_id
cross apply sys.dm_db_index_physical_stats( DB_ID(), idx.object_id, idx.index_id, NULL ,''LIMITED'') AS ips
where idx.[name] is not NULL
order by [fragmentation_percent] desc;'
 EXEC sp_executesql @Command

 FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor


Does dumping/restoring do the same as "vacuum full"? I have a large database running with PostgreSQL v11, and the disk space is very limited, so I have to VACUUM (FULL) the database frequently (e.g. every day).

But vacuuming is a very time-consuming operation. Furthermore, I often encounter vacuum error message such as

ERROR:  canceling autovacuum task
CONTEXT:  automatic vacuum of table ***

Fortunately, the service can be shutdown at a particular time point of the day, so I can dump the database after shutting it down, restore it immediately and restart the service again.

In addition, I found that "dumping/restoring" is faster than "vacuuming".

My question: Do the things that are done by "dumping/restoring" include the things what are done by "vacuuming full"?

If so, I can do it instead of vacuuming.

Yes, dumping and restoring the database will have the same effect as VACUUM (FULL). But having to do either of these every day is painful.

You have too little storage for your database. Artificially keeping it compact conserves space and speeds up queries, but it may slow down data modifications. The proper solution is to increase your storage and give your database some “breathing space”. That will make your problems go away, and probably reorganization like this is unnecessary.


How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure i am executing query like this on sql server 2008 R2

DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\certs\SSIS Disposition\SSIS Disposition\SSIS Disposition\Package1.dtsx"'

and getting error

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started:  12:08:21 AM
Error: 2014-03-21 00:08:21.71
   Code: 0xC0016016
   Source:
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
 the correct key is available.
End Error
Progress: 2014-03-21 00:08:22.66
   Source: Data Flow Task 1
   Validating: 0% complete
End Progress
Progress: 2014-03-21 00:08:22.69
   Source: Data Flow Task 1
   Validating: 50% complete
End Progress
Error: 2014-03-21 00:08:23.05
   Code: 0xC0202009
   Source: Package1 Connection manager "DestinationConnectionOLEDB"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'wm_bulk'.".
End Error
Error: 2014-03-21 00:08:23.07
   Code: 0xC020801C
   Source: Data Flow Task 1 Destination - ACQ_DailyFeedStaging [220]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before thi
s with more information on why the AcquireConnection method call failed.
End Error
Error: 2014-03-21 00:08:23.07
   Code: 0xC0047017
   Source: Data Flow Task 1 SSIS.Pipeline
   Description: component "Destination - ACQ_DailyFeedStaging" (220) failed validation and returned error code 0xC020801C.
End Error
Progress: 2014-03-21 00:08:23.07
   Source: Data Flow Task 1
   Validating: 100% complete
End Progress
Error: 2014-03-21 00:08:23.07
   Code: 0xC004700C
   Source: Data Flow Task 1 SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2014-03-21 00:08:23.07
   Code: 0xC0024107
   Source: Data Flow Task 1
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  12:08:21 AM
Finished: 12:08:23 AM
Elapsed:  1.544 seconds
NULL

It looks as though the package has been saved with sensitive data protected, meaning that it can only be run by specified users.

To fix it, set the PackageProtectionLevel to DontSaveSensitive and then re-deploy the package to the server.


Slow running query with inner SELECT in where clause I have a query that looks like the following:

USE DATABASE3;
GO
SELECT t1.COL1 AS COLUMN1,
      t2.COL2 AS COLUMN2,
      t1.COL3,
      t1.COL4 AS COLUMN4,
      t1.COL5 AS COLUMN5,
      t1.COL6,
      t1.COL7,
      t1.COL8,
      t1.COL9,
      t1.COL10,
      t1.COL11,
      t1.COL12,
      t1.COL13,
      t1.COL14,
      t1.COL15,
      t1.COL16,
      t1.COL17,
      COL18,
      COL19
FROM dbo.TABLE1 t1 with (nolock)
INNER JOIN dbo.TABLE2 t2 with (nolock)
ON t1.COL1 = t2.COL1
WHERE  (t1.tran_type_cd = [smallint value])
            and t2.COL2 in (SELECT COL20 FROM database1.dbo.TABLE3 with (nolock)
                        WHERE     (COL21 = 'varchar 250 text'))
            and t1.COL3 >= (select COL22
                              from database2.dbo.TABLE4 with (nolock)
                              where COL23 = (SELECT COL24 FROM dbo.TABLE5 with (nolock)))
            and t1.COL3 <= (select COL25
                              from database2.dbo.TABLE4 with (nolock)
                              where COL23 = (SELECT COL24 FROM dbo.TABLE5 with (nolock)))
            and t2.COL26 = (SELECT COL24 FROM dbo.TABLE5 with (nolock))

and it's running ridiculously slow.  Here's a quick rundown on some relevant details:

The following tables both just have one record -

database2.dbo.TABLE4
database3.dbo.TABLE5

t1.COL3
Table4.COL22
Table4.COL23
Table4.COL25
Table5.COL24
t2.COL26

are all datetime fields.

What's weird is that when I put in exact dates for instead of the inner SELECTs

t1.COL3 >= 'exact date'
t1.COL3 <= 'exact date'
t2.COL26 = 'exact date'

the query runs instantaneously.

Initially thinking it's utilizing a bad execution plan, I've cleared the plan cache via the following t-sql:

DBCC FREEPROCCACHE;

Also, couldn't view the execution plan of the complex query because it's just keeps running & running.  This is a production server so a reboot needs to be avoided if at all possible.

Any help is greatly appreciated.  Thank you!

P.S. Sorry for the bad formatting with the sql statement...still trying to figure things out around here.

Try this:

USE database2;
go
CREATE INDEX ix4_23 ON TABLE4(Col23) INCLUDE (Col22, Col25);
go
USE database1;
go
CREATE INDEX ix3_21 ON TABLE3(Col21) INCLUDE (Col20);

And in the sub-queries put "TOP (99999999)" in there. You want to persuade the sub-queries to pull all the rows from the other databases separately. TOP with a number should persuade it... :)


SQL Server 2017 Enterprise, DB has suspect_pages, GAM and SGAM NOT ALLOCATED - am I OK? I realize I may get some "horror!" reactions, but bear with me...

There is but one suspect_pages row, and I plugged the info into DBCC PAGE (output shown below).  When I saw that both GAM and SGAM show "NOT ALLOCATED", my theory became, "I need not care about it..."

We had the DB up to 5 TB with masses of data to verify.  At least 1TB was TRUNCATEd at the exact time when the suspect_pages row was created - all from ONE table.

Having had suspect_pages rows appear during a similar exercise on the same DB several months prior, where DBCC PAGE showed that none of the pages cited existed in the database, we moved on, deleting the suspect_pages rows.

The IndexID is -1, the ObjectID is 0.  To me, this means, "there's nothing to see here..."

In the opinion of the experts here, does it look as though it is safe to delete the current suspect_pages row?  Is there a DBCC CHECKDB REPAIR% option I should use?

With thanks, and hoping for at least minimal "nice abuse"...

Command:

DBCC TRACEON (3604);
DBCC PAGE ('DB_US', 1, 731783305, 3) WITH TABLERESULTS;
DBCC TRACEOFF (3604);
GO

Produces an error:

(43 rows affected)
Msg 2514, Level 16, State 8, Line 6
A DBCC PAGE error has occurred: Invalid page metadata - dump style 3 not possible.

DBCC PAGE OUTPUT - using output level ZERO and no WITH...:

PAGE: (1:456793585)

BUFFER:

BUF @0x000002F2DA54D680

bpage = 0x00000246815A2000          bhash = 0x0000000000000000          bpageno = (1:731783305)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 56821                       bstat = 0x809
blog = 0x15ab215a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0

PAGE HEADER:

Page @0x00000246815A2000

m_pageId = (1:456793585)            m_headerVersion = 1                 m_type = 2
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x2200
m_objId (AllocUnitId.idObj) = 235350m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057609461825536                                Metadata: PartitionId = 0
Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (1:456793584)
m_nextPage = (1:456793586)          pminlen = 29                        m_slotCnt = 168
m_freeCnt = 32                      m_freeData = 7824                   m_reservedCnt = 0
m_lsn = (722027:450682:278)         m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0                   m_tornBits = -1231952543            DB Frag ID = 1

Allocation Status

GAM (1:731572992) = NOT ALLOCATED   SGAM (1:731572993) = NOT ALLOCATED  PFS (1:731777976) = 0x0   0_PCT_FULL
DIFF (1:731572998) = CHANGED        ML (1:731572999) = NOT MIN_LOGGED

To quote Paul Randal on the matter:

  We’re interested in the output beginning Metadata:. Those fields are
  not stored on the page itself...

  If you see the ObjectId is 0, that means there was no metadata found.
  This could be because:

  The table that the page was part of has been deleted since the page
  corruption was logged
  The system catalogs are corrupt in some way
  The
  page is corrupt and so incorrect values were used to look up the
  metadata

  In any case, you’ll need to wait for DBCC CHECKDB to complete to know the extent of the damage.

It doesn't sound like you're in the clear, so I'd probably push on getting a suitable environment to check a full backup on.


DMV queries for capturing most frequently used queries and worst performing queries Can anyone provide dmv query to achieve the following

Query to capture frequently used stored procedure
Query to capture frequently used ad-hoc queries
Query to capture worst performing stored procedures
Query to capture worst performing queries

Thanks in Advance.

'Worst performing' query depends on how you would define the word 'worst'. Is it by Duration? CPU? I/O?

For measuring highest CPU utilization of my cached query plans, I use the dm_exec_query_stats and dm_exec_sql_text DMVs.

The following query gets everything from the plan cache that was executed within the last day. It then calculates the percentage of overall CPU utilisation of that query and orders the results by that percentage.

It's an idea that I borrowed from an Itzik Ben-Gan post, although he used Extended Events to capture data

  SELECT
     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext

  FROM sys.dm_exec_query_stats AS deqs
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE())
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')
  GROUP BY
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time, deqs.query_hash, dbid

  ORDER BY pctcpu DESC

Caveats

It does, however, rely on the plan being cached (not always the case), the CPU utilisation is the total from the PlanCreationTime not just the last day, and I only really use it as a rough guide for where to start looking for my issues.

However, when captured over time, it has enabled me to add/remove indexes and tweak queries to improve their performance.

Have a play with the queries and look up the definitions of the two DMVs I've mentioned above to try and get the results to your questions.


Show All online SID Database Oracle 11 in Linux SuSe and Find Database file I'm new with Oracle DBA in my new office, and they gave me a development server that already have a lot SID database online in SuSe (I checked with lsnrctl status)

My questions are :

How to check what SID instance that really live/online, because turns out when I checked again with ps ax|grep ora, only 3 of databases live/online?

If only 3 SID databases that really online like in 1st question, how do I know where all the 3 SID the database file position that already live/online? what do I have to type to know it?

I try to login sqlplus sys as sysdba when I checked with select name from v$database; I was logging in 1 SID database. Continuing from 2nd question, How do I logout from current SID database and then switch  to other 2 SID database?
(example in MongoDB, I type use tutorial to enter tutorial database and type use dbdevelopment to switch to database dbdevelopment)

cat /etc/oratab

As a DBA, you need to learn the Oracle Data Dictionary.

"One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database"
SQL>select name from v$datafile;

# set environment
. oraenv <<< SID1;
# login as sys user (IPC)
sqlplus / as sysdba
# list datafiles
SQL>select name from v$datafile;
SQL>exit
# repeat for SID2 and SID3

Side note.
One Oracle Database System (SID) can serve multiple databases (schemas). It is expensive, in terms of resources, to have multiple systems running.
# create database 1
SQL>create user DB_OWNER_1 identified by 'pass';
SQL>create table db_owner_1.t (x char(1));
# create database 2
SQL>create user DB_OWNER_2 identified by 'pass';
SQL>create table db_owner_2.t (x char(1));

# login as 'app user A' who has privileges to manipulate tables (DML) in database 1.
sqlplus appuserA/pass@TNS_ALIAS_1
SQL>alter session set current_schema = "DB_OWNER_1"
SQL>insert into t values ('x');

# login as 'app user B' who has privileges to manipulate tables (DML) in database 2.
sqlplus appuserB/pass@TNS_ALIAS_2
SQL>alter session set current_schema = "DB_OWNER_2"
SQL>insert into t values ('x');

You now have two applications operating on two different databases served by the one and same Oracle Database System.
Best of luck!


File Group Association while Snapshop Replication I want to know how can I save filegroup structure during snapshop replication in SQL Server? Does this property solve my problem? I want the same filegroups in publishers be created in the subscriber.

Thanks

The information text in the description of the item property provides you with the solution to your question:
(emphasis mine)

Copy file group associations
Determines whether to script the associations between file groups and published objects. The file groups must already exist at the Subscriber.

If the file groups don't exist at the Subscriber and you set the option Copy file group associations to True, then the replication will fail, because it will expect the Subscriber to have identical file groups. All transferred objects will be transferred with an additional ...filegroup... parameter, depending on the object that has to be transferred.
If this option is set to false then the objects will be transferred without an additional ...filegroup... parameter, depending on the object being transferred. The replication will succeed, even if the Subscriber has different file groups, because the objects will be replicated to the default file group.
Proving It
I created a StackExchange database with the following files and file groups and added a new file group for this question:

CREATE DATABASE [StackExchange]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'StackExchange', FILENAME = N'C:\SQL\SQL_DATA\StackExchange.mdf' , SIZE = 56320KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [DEFAULTRO]
( NAME = N'StackExchange_DefRO', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeRO.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [PUBL_SNAP_DBASE]  DEFAULT
( NAME = N'StackExchange_PUBL_SNAP', FILENAME = N'C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'StackExchange_log', FILENAME = N'C:\SQL\SQL_LOGS\StackExchange_log.ldf' , SIZE = 112384KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

This database already contains various tables and stored procedures from answering previous questions on DBA.SE. They were created in the PRIMARY file group. Here an extract:
Input

SELECT DS.name AS DataSpaceName
      ,AU.type_desc AS AllocationDesc
      ,AU.total_pages / 128 AS TotalSizeMB
      ,AU.used_pages / 128 AS UsedSizeMB
      ,AU.data_pages / 128 AS DataSizeMB
      ,SCH.name AS SchemaName
      ,OBJ.type_desc AS ObjectType
      ,OBJ.name AS ObjectName
      ---,IDX.type_desc AS IndexType
      ---,IDX.name AS IndexName
FROM sys.data_spaces AS DS
     INNER JOIN sys.allocation_units AS AU
         ON DS.data_space_id = AU.data_space_id
     INNER JOIN sys.partitions AS PA
         ON (AU.type IN (1, 3)
             AND AU.container_id = PA.hobt_id)
            OR
            (AU.type = 2
             AND AU.container_id = PA.partition_id)
     INNER JOIN sys.objects AS OBJ
         ON PA.object_id = OBJ.object_id
     INNER JOIN sys.schemas AS SCH
         ON OBJ.schema_id = SCH.schema_id
     ---LEFT JOIN sys.indexes AS IDX
         ----ON PA.object_id = IDX.object_id
            ----AND PA.index_id = IDX.index_id
WHERE OBJ.type = 'U'
AND OBJ.type_desc = 'USER_TABLE'
AND OBJ.name LIKE 'Q%'
ORDER BY DS.name
        ,SCH.name
        ,OBJ.name
        --,IDX.name

Output

+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DataSpaceName | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType |          ObjectName           |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DEFAULTRO     | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q117478_Encryption_Provider   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q153379_TestTable             |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_Customer              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD                   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD_Purchase          |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q190497_Stats_Hash            |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196570_Unicode               |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q206094_shift                 |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation    |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation_ws |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_1        |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_2        |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+

Observations
As you can see all my tables related to questions on DBA.SE have been created in the PRIMARY filegroup. (Ok, nearly all of them.)
If I were to create a new table, then it would be created in the DEFAULT file group, which is no longer PRIMARY but the one named [PUBL_SNAP_DBASE].
Snapshot Replication
I have created a snapshot replication on my local machine which will replicate any changes from the StackExchange database to the StackExchangeSubscriber database.
The settings for the All Tables Properties - Copy file group associations has been set to TRUE, which should result in an error in the replication.
The Subscriber Database

CREATE DATABASE [StackExchangeSubscriber]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'StackExchangeSubscriber', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeSubscriber.mdf' , SIZE = 56320KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'StackExchangeSubscriber_log', FILENAME = N'C:\SQL\SQL_LOGS\StackExchangeSubscriber_log.ldf' , SIZE = 112384KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

The subscriber database doesn't have the [DEFAULTRO] file group, nor does it contain the newly created [PUBL_SNAP_DBASE] one.
Verifying the Replication Status

Everything looks good. Let's continue with a modification...
Modifying the Published Database
Let's go ahead and create a new table in the published database StackExchange using DDL. First we'll check the objects in the target/subscriber database with the same script from above.
Output

+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DataSpaceName | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType |          ObjectName           |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q117478_Encryption_Provider   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q153379_TestTable             |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_Customer              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD                   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD_Purchase          |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q190497_Stats_Hash            |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196570_Unicode               |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q206094_shift                 |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation    |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation_ws |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_1        |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_2        |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+

Looks the same except for the object that was created in the [DEFAULTRO] file group, which wasn't replicated. Hmmm. I think that is a hint of things to come.
Create Table via DDL
USE StackExchange
GO
CREATE TABLE Q221890 (Q221890_ID INT, Q221890_Text VARCHAR(20))

I didn't define the file group, so it should be created in the current DEFAULT file group, which is? Exactly.

+-----------------+----------------+-------------+------------+------------+------------+------------+------------+
|  DataSpaceName  | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType | ObjectName |
+-----------------+----------------+-------------+------------+------------+------------+------------+------------+
| PUBL_SNAP_DBASE | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q221890    |
+-----------------+----------------+-------------+------------+------------+------------+------------+------------+

I did not define the file group which means the table will be created in the newly defined [PUBL_SNAP_DBASE] file group, because it is the new default.

The replication will not replicate the new table, because of the Copy file group associations setting of True in the All Tables Properties.

Insert Data

USE [StackExchange]
GO
INSERT INTO [dbo].[Q221890]
           ([Q221890_ID]
           ,[Q221890_Text])
     VALUES
           (1
           ,'Test')
GO

Check Data

+------------+--------------+
| Q221890_ID | Q221890_Text |
+------------+--------------+
|          1 | Test         |
+------------+--------------+

Checking StackExchangeSubscriber Database in Replication Monitor

As can be seen in the monitor error message, the new object is created with the reference to the non-existing file group [PUBL_SNAP_DBASE] which causes an error at the subscriber.
Turn Off the Setting and Retry
We'll turn of the file group setting Copy file group associations and re-initialize the snapshot replication. Let's check the list of objects with the script:

+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| DataSpaceName | AllocationDesc | TotalSizeMB | UsedSizeMB | DataSizeMB | SchemaName | ObjectType |          ObjectName           |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q117478_Encryption_Provider   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q153379_TestTable             |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_Customer              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD                   |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q184469_DVD_Purchase          |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q190497_Stats_Hash            |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196570_Unicode               |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q196719_Filegroup_2           |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q206094_shift                 |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar              |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation    |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q214251_nvarchar_collation_ws |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Q221890                       |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_1        |
| PRIMARY       | IN_ROW_DATA    |           0 |          0 |          0 | dbo        | USER_TABLE | Qxxxxx_Join_Question_2        |
+---------------+----------------+-------------+------------+------------+------------+------------+-------------------------------+

Yes, the new table in the file group has been replicated.

Answering Your Question

I want to know how can I save filegroup structure during snapshot replication in SQL Server?

You can't. (See answer further down)

Does this property solve my problem?

No, which was proved with the above procedure

I want the same filegroups in publishers be created in the subscriber.

You will have to create the file groups at the target/subscriber before you create the objects in those file groups.
Turning on the option will cause errors, if the file groups do not exist.


Does using Retry attempt option SQL Agent job create performance issue I want to use Retry attempt option of sql agent job, want to know does it hold the resource and will create performance issue, if i keep it every 60 minutes

The retry process itself does not use resources: it basically idles for the specified amount of time, then restarts the step. The step itself uses resources.


Altering column from INT to BIG INT and fill factor trick I have inherited a script that rebuilds the fill factor sitting at 90 fill factor on the INT primary key. It rebuilds it at a fill factor of 50. Then the script drops the index, alters the column to a BIGINT and rebuilds the fill factor back to its original 90.

Does that make sense to anyone?  Are they doing this to grow space?  Seems like as soon as they drop the clustered index, the space is a moot point. Thoughts on this?

I have to tell you the only answer I can come up with is "This makes no sense what-so-ever".

From what I can tell they were expanding the table out so there was room for the additional space for the BIGINT.  They then dropped the primary key (presumably the clustered index as well) to avoid some of the time spent increasing the column width then re-created the primary key.

I've never tried this so I can't tell you how long it would take but I can tell you after a great deal of testing which way I went on a similar task.

Script and drop all indexes except the clustered index.
Create a temp table with the correct structure
Copy the data from the existing table to the temp table
Rename swap
Re-create required indexes
Drop original table

I wrote about the logic of it in more detail here.


How can I find state name which has the most vehicle accidents in the database? I have a table "ACC_DATA" that has the following columns: ACCIDENT_ID, STATE_CODE, ACC/DATE and a second table "US_STATES" which has the following columns: STATE_CODE and STATE_NAME

Each accident that happened is given an ID and the State in which it occurred, in the "ACC_DATA" table. How can I use SQL to find the name of the State which had the most accidents?

This is all that I have so far:

SELECT AD.STATE_CODE, COUNT(*) AS FREQUENCY
FROM ACC_DATA AD, US_STATES US
GROUP BY AD.STATE_CODE
ORDER BY COUNT(*) DESC

But this only gives a table of State codes with number of accidents in each. I am not quite sure how to join the two tables to produce the state name with a single max value.

A join should do, and then select the state name instead.

SELECT US.STATE_NAME, COUNT(*) AS FREQUENCY
FROM ACC_DATA AD, US_STATES US
WHERE AD.STATE_CODE = US.STATE_CODE
GROUP BY US.STATE_NAME
ORDER BY COUNT(*) DESC


Using of OPTIMIZE FOR hints in a sql query as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing.

For such a stored procedure:

SELECT * FROM Tabele WHERE param=@param1

We have two parameter S1 with high selectivity and s2 with low selectivity.

If we are using such a hint:

OPTION(OPTIMIZE FOR @param1=s1)

and then send s2 to stored procedure, we have still parameter sniffing.

Now I have a conceptual question!:

How doe's OPTIMZE FOR hint help us actually to avoid parameter sniffing?

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.


LOAD DATA mysql command and use only one column I have table t1 with three columns, and a file per line a text.
my tables contains id,sura and aya fields. I need to fill only aya from File.
I use the following command:
load data infile '/home/mohsen/codes/haq/translation-tmp/fa.ghomshei.txt' into table tr_fa_ghomshei LINES TERMINATED BY '\n' ;

Now question is :
How can I limit to aya field?

You can do :
load data infile '/home/mohsen/codes/haq/translation-tmp/fa.ghomshei.txt' into table tr_fa_ghomshei LINES TERMINATED BY '\n' (aya);


Order by is ignored if inside a derived table of a update (only in MYSQL 5.7) I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ignored. So, the rows are updateds based in primary key, not in order by.

May someone help me? Why the order by is ignored in MYSQL 5.7 and how to fixed it?

Thanks!!!

OLD POSITION VALUES

ImageId     Position
389         0
390         6
391         4
392         1

NEW POSITION VALUES, AFTER FIXED (MYSQL 5.5)

ImageId     Position
389         1
390         4
391         3
392         2

NEW POSITION VALUES, AFTER FIXED (MYSQL 5.7)

ImageId     Position
389         1
390         2
391         3
392         4

UPDATE

UPDATE  Image as t1
        INNER JOIN  (
                    SELECT  t.*, @rownum := @rownum + 1 AS newPosition
                    FROM    Image t, (SELECT @rownum := 0) r
                    ORDER   BY t.Position
                    ) as td
            ON t1.ImageId = td.ImageId SET     t1.Position= td.newPosition

A derived table in SQL is a set, and a set by definition has no row order. Your attempt to use an imperative calculation (@rownum := @rownum + 1) within a set was wrong to begin with, and while it may have worked for a while, as you now realized, there is no guarantee for that. Most database engines will (correctly) raise an error if you try to use a similar query.

The need that you are describing was addressed with window ranking functions which are supported as of MySQL 8. If you can upgrade to that, your solution will be easy and elegant with ROW_NUMBER () OVER(PARTITION BY ImageID ORDER BY Position).

If you can't use version 8, your only guaranteed solution is to use a correlated subquery, something along the lines of:

Disclaimer - Since you didn't provide DDL and sample data, the solution below isn't tested

UPDATE  Image as t1
SET     Position = (
                    SELECT COUNT(*)
                    FROM Image AS t2
                    WHERE t1.ImageID = t2.ImageID
                          AND
                          t1.Position <= t2.position
                    );

Note that for large tables, this solution may not perform well, but it is guaranteed to work regardless of version as it follows SQL set rules.

This is valid ANSI SQL syntax. MySQL specifically may require some adjustments.


Aggregates per half hour intervals within a time span Consider the following table:

CREATE TABLE dbo.Events (
    [ClientID] int,
    [EventID] int,
    [EventName] varchar(50),
    [StartDate] datetime,
    [Duration] bigint
)

INSERT INTO dbo.Events(ClientID, EventID, EventName, StartDate, Duration)
VALUES (1, 1, 'Login', '2016-11-27 01:30:00.000', 86400000),
       (2, 1, 'Login', '2016-11-27 00:30:00.000', 86400000),
       (3, 1, 'Login', '2016-11-27 00:00:00.000', 86400000),
       (4, 1, 'Login', '2016-11-28 23:30:00.000', 172800000),
       (1, 2, 'Lock', '2016-11-27 23:30:00.000', 3600000),
       (4, 2, 'Lock', '2016-11-28 23:30:00.000', 1800000)

As you can see, the Duration is in milliseconds and can span multiple days.

I need to see how many clients were Logged In, and how many clients were Locked, and for how long during a given date range. The date range can span a day/week/month/year, and when it does span more than a day, it should still bucket by half hour.

In the end, the results for a query on the date range 2016-11-28 00:00:00.000 -  2016-11-30 00:00:00.000 would look like so (apologies if I miscalculated the SumDuration or CountClients somewhere, I did this manually but I think it is correct):

EventID  EventName  HalfHour  SumDuration  CountClients
--------------------------------------------------
1        'Login'    0:00      5400000      3
1        'Login'    0:30      3600000      2
1        'Login'    1:00      3600000      2
1        'Login'    1:30      1800000      1
...      ...        ...       ...          ...
1        'Login'    23:00     1800000      1
1        'Login'    23:30     3600000      1
2        'Lock'     0:00      3600000      2
2        'Lock'     0:30      1800000      1
...      ...        ...       ...          ...
2        'Lock'     23:00     1800000      1
2        'Lock'     23:30     3600000      2

The ellipses are for the sake of brevity in repeated results, but there should be 48 results per Event (1 for every half hour in a day). You will also notice that the Duration needs to be calculated during that half hour. It is not in the example data, but it is possible to have a Duration end at any time, not just a half hour mark. Last, I should note the result for 'Login' at 23:30. ClientID 4 had a Login event for both 2016-11-28 and 2016-11-29 during the 23:30-0:00 half hour. So, the Duration sums both, but only counts the Client once.

I have the following, which gives me the CountClients and SumDuration, but doesn't break it up into half-hour increments:

select E.EventID,
       E.EventName,
       count(*) as CountClients,
       sum(datediff(millisecond, I.StartDate, I.EndDate)) as SumDuration
from dbo.Events as E
  cross apply (
              select max(T.StartDate) as StartDate,
                     min(T.EndDate) as EndDate
              from (
                   values(@StartDate, @EndDate),
                         (E.StartDate, dateadd(millisecond, E.Duration, E.StartDate))
                   ) as T(StartDate, EndDate)
              ) as I
where E.StartDate < @EndDate and
      dateadd(millisecond, E.Duration, E.StartDate) > @StartDate
group by E.EventID,
         E.EventName;

How would I go about breaking this up into half hour increments?

First, let's look at how to get all the time slices.

DECLARE @FirstDay smalldatetime = '20161128',
        @LastDay  smalldatetime = '20161129';

SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
  DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
FROM master..spt_values;

This will give you your set of 96 time slots, in half hour increments, from the 28th at midnight through the 29th at 23:30. A Numbers table is a better option than master.dbo.spt_values, but you can also use sys.all_objects - or any table really that has enough rows to cover your desired date range.

Next, let's see how to find the rows in the Events table that you care about - basically anything that passes through your window.

DECLARE @FirstDay smalldatetime = '20161128',
        @LastDay  smalldatetime = '20161129';

SELECT EventID, EventName, StartDate,
  EndDate = DATEADD(MILLISECOND, Duration, StartDate)
FROM dbo.Events
WHERE StartDate >= DATEADD(MILLISECOND, -Duration, @FirstDay)
  AND StartDate <  DATEADD(DAY, 1, @LastDay);

Now, how to use that to get the results you're after (edited to accommodate durations that do not end on the half hour mark):

DECLARE @FirstDay smalldatetime = '20161128',
        @LastDay  smalldatetime = '20161129';

;WITH TimeSlots(HalfHour) AS
(
  SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
    DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
  FROM master..spt_values
),
EventRange AS
(
  SELECT EventID, EventName, StartDate,
    EndDate = DATEADD(MILLISECOND, Duration, StartDate)
  FROM dbo.Events
  WHERE StartDate >= DATEADD(MILLISECOND, -Duration, @FirstDay)
    AND StartDate <  DATEADD(DAY, 1, @LastDay)
),
Combo AS
(
  SELECT e.EventID, e.EventName,
    HalfHourSlot = CONVERT(CHAR(5), CONVERT(datetime, CONVERT(time, t.HalfHour)), 108),
    CASE
        WHEN e.StartDate > DATEADD(minute, 30, t.HalfHour) OR e.EndDate <= t.HalfHour
            THEN 0
        ELSE
            DATEDIFF(MINUTE, CASE WHEN e.StartDate >= t.HalfHour THEN e.StartDate ELSE t.HalfHour END, CASE WHEN e.EndDate <= DATEADD(minute, 30, t.HalfHour) THEN e.EndDate ELSE DATEADD(minute, 30, t.HalfHour) END)
    END AS Duration
  FROM TimeSlots AS t
  CROSS JOIN EventRange AS e
)
SELECT EventID, EventName, HalfHour = HalfHourSlot,
  SumDuration  = SUM(Duration),
  CountClients = SUM(CASE WHEN Duration>0 THEN 1 ELSE 0 END)
FROM Combo
GROUP BY EventID, EventName, HalfHourSlot
ORDER BY EventName DESC, HalfHour;


Innodb Isolation levels and locking performance I am reading a lot about locking and isolation levels in MySQL and InnoDB.

I understood the technical differences and basic approach, but I still fail to understand a situation where for example READ UNCOMMITTED will give me better performance than READ COMMITTED.

Here is my simulation:

CREATE TABLE `t` (
    `i1` INT(11) NOT NULL,
    `i2` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`i1`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
insert into t values(10,10), (20,20), (30,30), (40, 40), (50,50),(60,60);

T1:

MariaDB [maint]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> update t set i2=100 where i1=60;
Query OK, 1 row affected (0.00 sec)

T2 with READ UNCOMMITTED:

MariaDB [maint]> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> select i2 from t where i1 =60;
+------+
| i2   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

So the logic as expected, and no locks.

T2 with READ COMMITTED:

MariaDB [maint]> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> select * from t where i1 =60;
+----+------+
| i1 | i2   |
+----+------+
| 60 |   60 |
+----+------+
1 row in set (0.00 sec)

The logic works as I expected, and again no lock.

So simply the only difference is that READ UNCOMMITTED just took the data before commit and READ COMMITTED took a snapshot.

Can someone help me create a simulation or explain a situation in which UNCOMMITTED will have no locks whereas COMMITTED would have? Same for REPEATABLE READ vs COMMITTED.

In my opinion...  If you are tweaking isolation levels for performance then you have inefficient/inadequate code/schema/indexes and should focus on that instead.

Think about it...  You can perform hundreds of 'simple' queries per second from dozens of connections.  So, if your queries are 'simple' and you don't have too many of them, then they will rarely collide -- hence, the isolation mode won't matter!

select i2 from t where i1 =60; -- The obvious index is INDEX(i1).  Better would be PRIMARY KEY(i1), but that may not be appropriate.  If i1 were not the PK, then the best is a "covering" index:  INDEX(i1,i2) -- the query is performed in the index, not touching the data.

You will be hard pressed to run enough of the queries you mentioned fast enough from enough connections to have them stumble over each other.  In fact, you are more likely to be blocked by the "Query cache".  For serious production work, that is usually turned completely off:  query_cache_size = 0 and query_cache_type = 0.

Back to my first point...  Changing the Isolation Level changes the results that you might get from queries!  So, doing it for performance reasons could mess with the application output.


Query Performance depending on parameter 21seconds vs. > 14hours I have the following query:

SELECT DISTINCT DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))),
                [DATE],
                DATEPART(YY,[DATE]),
                DATEPART(MM,[DATE]),
                DATENAME(MONTH,[DATE]),
                DATEPART(DD,[DATE]),
                [Time],
                DATEPART(HH,[Time]),
                DATEPART(MI,[Time]),
                DATEPART(SS,[Time])
FROM [SourceTable]
WHERE ([DATE] BETWEEN '2012-01-08' AND '2012-01-08') AND
       DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))) NOT IN
       (SELECT [DateTime]
        FROM [DIM].[DateTime]
        WHERE [Date] BETWEEN '2012-01-08' AND '2012-01-08')
ORDER BY [DATE], [Time]
OPTION (MAXDOP 6)

When I use this specific date I had to cancel the query after 14hours running time, any other date the same query takes normally 21 seconds. I did check the query execution plan independent on what date I use it is the same, the amount of rows impacted by the query is for each day nearly the same (around 500'000 Rows). I checked the indexes and the statistics are up to date. How is such a behaviour explainable and how to fix this ?

The Hardware of the machine is sufficient (8 Cores, 192 GB RAM)

I don't know about sqlserver but in an Oracle database, I would suggest making a trace of the sql execution, one that includes all waits and events that cause the query to spend time. This shows the exact circumstances where the sql is executing in and that might be very different than those in the environment where you did the explain plan. Sqlserver without doubt has a similar feature to show the real execution, including waits.

In Oracle we have sql plan stability. Maybe sqlserver has something similar? In that case, try to use that.


MySQL Unique Index Keylength I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key.

The engine of choice here is InnoDB.

There are about 2 million entries in Table A with my unique-key 256 bit key.

Table B, with 7 million records, is related to table A with the 256 bit foreign key.

My question is, instead of 64 char. length keys, should I opt for 160
bit (40 digit) keys?

Will I see a large performance boost? Or the difference would not be
too large?

My machine has limited computation power and although scripting and programming is quite easy, it would take me hours to compute and re-calculate for 160 bit from 256, which is the reason for my question.

Large PRIMARY KEYs and UNIQUE KEYs are a very bad idea for InnoDB. If you want a real performance boost for subsequent retrieval, you must use smaller PRIMARY KEYs. Why?

The smaller the PRIMARY KEY, the better things will be
Secondary indexes have a primary key associated with each secondary key. Bigger PRIMARY Key, bigger Secondary Index. and Vice-Versa
Caching improves with smaller primary keys

Here is what you should do with 64-char keys

STEP01) Create a Key Table for 64-character keys

Start with a table that will hold the 64-character key and associate it with an auto incremented field

CREATE TABLE big64keytable
(
    bigkey   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    big64key CHAR(64),
    PRIMARY KEY (bigkey),
    UNIQUE KEY (big64key)
) ENGINE=MyISAM;

STEP02) Create a Stored Function to Get numeric ID for the 64-character key

First, create the SQL to INSERT your 64-character key and then retrieve the numeric ID

SET @givenkey = '8g4gbf3g7b5gf4n3gfn8g927534';
INSERT IGNORE INTO big64keytable (big64key) VALUES (@givenkey);
SELECT bigkey FROM big64keytable WHERE big64key = @givenkey;

Now take this simple retrieval algorithm and place it in a Stored Function

DELIMITER $$

DROP FUNCTION IF EXISTS `GetBigKey` $$
CREATE FUNCTION `GetBigKey` (GivenKey CHAR(64)) RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN

    DECLARE rv INT UNSIGNED;

    INSERT IGNORE INTO big64keytable (big64key) VALUES (GivenKey);
    SELECT bigkey INTO rv FROM big64keytable WHERE big64key = GivenKey;
    RETURN rv;

END $$

DELIMITER ;

Going forward, simply generate a numeric unsigned ID  (4 bytes ) for each 64-character key.

Here are two of my past posts in the DBA StackExchange on other large PRIMARY KEY issues

Dec 22, 2011 : File name as primary key?
Oct 18, 2012 : MySQL: Unique constraint on large column


Designing an Accumulating Snapshot with Repeated Groups, need advise Situation

I need to create a pie chart that has to be able to be represented at the following levels: District, Region, and School.

At the student level it has to show the trend of the student over a set of periods: "I1", "G1", "I2", "G2", "I3", "G3", "I4", "G4"

The pie chart will show a number and pct of students with 1,2,3, or none risks factors. Risk factors being: Academics, Discipline, and Attendance. I have been given a formula to determine if a student is at risk at any of the categories above.

We have a Ralph Kimball based approach Data Warehouse.

I have a working query at the school level that I am able to plug the given formula through subselects and sums/counts that returns me as follows:

STUDENT_ID  STUDENT_NAME  PERIOD SCHOOL_YEAR  ACD_RISK  DISC_RISK  ATT_RISK
1           Viriato       I1     2010/2011    1         1          1

1           Viriato       G1     2010/2011    1         0          0

2           Teodosio      I1     2010/2011    0          1         0

.....................

The query that produces me the total number of students having 1,2,3 or 0 risk factors by doing a count sum on the output above works fine as far as performance is concerned. However when I run it at the region and district levels, performance is not acceptable.

Solution

I need to create an Accumulating Snapshot

Concerns

Ralph Kimball in "The Complete Guide to Dimensional Modeling" chapter 12 page 244 states on Accumulating Snapshot:

  A single row represents the complete history of something.

Solution A: Prior to Reading Kimball

So should I have a job that produces a table with the resultset above. But then I would have 8 rows per student per year. I would not have a single row like Kimball suggests.

Solution B: After Reading Kimball

student_id,

name,

school_year,

period,

att_risk_I1,

adm_risk_I1,

disc_ris_I1,

att_risk_G1,

adm_risk_G1,

disc_ris_G1,

.

.

.

.

att_risk_G4,

adm_risk_G4,

dis_ris_G4

As you can see with solution B I would have one row per student per year, and I could generate a trend graph for that student all the way from elementary school to high school with 12 rows (Grades 1st through 12th). However I am not sure I like having ATT_RISK, DISC_RISK and ADM_RISK repeated 8 times.

Which approach would you take?

It's not really clear what is asked here but I think your solution is simplest when you have one row per student.  That allows further aggregating later however you want to do it.

So this is a vote for b.


What is the correct way to present the disks to ASM? Hello I would like to know which is the correct way ... or the most recommended way to present the disks to ASM, for example: I have 3 disks mapped to a server (rhel7) orasys, orafra, oradata, with dm-multipath I configure the multipath and I put aliases (orasys, orafra and oradata respectively). The doubt I have from there, I have read in the guides, that the disks are formatted, leaving for examples: orasys1, orafra1, oradata1 and these are the ones that are presented to ASM as /dev/mapper/* and with the respective permissions ... but when I do it that way it gives an error finishing the installation of the grid because it cannot create the diskgroup, however if I do not format them, that is, I present them directly as orasys, orafra and oradata I have no problem.
The database is oracle 19c and I don't use asmlib but udev rules.
Thanks.

How did you "format" them? Oracle expect them to be block devices, not formatted with a file system. Did you read this documentation?

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ladbi/configuring-device-persistence-manually-for-oracle-asm.html#GUID-70D50812-CCB2-41E4-AA3B-4689E1DA934E
https://oracle-base.com/articles/linux/udev-scsi-rules-configuration-in-oracle-linux#load_updated_block_device_partitions"
https://dba010.com/2019/07/22/udev-rules-for-configuring-asm-disks/
https://www.thegeekdiary.com/centos-rhel-7-how-to-set-udev-rules-for-asm-on-multipath-disks/


Log non indexed queries for multiple databases I have about 50 MySQL databases, each with more than 20 tables. My Sort_rows, handler_read_rnd_next, and handler_read_first stats are really high.

I can't add indexes manually for every table if not necessary, and could miss some, so I'm looking for a tool or a configuration which could help me to find which rows / requests have missing indexes.

Which tools are you using, or how should I configure MySQL to tell me more about these non indexed queries?

To find all queries not using indexes, you need to do two things:

Enable the slow query log
Set the global variable log_queries_not_using_indexes to ON

In my.cnf for 5.6, this would look like:

[mysqld]
...
...
slow_query_log=ON
log_queries_not_using_indexes=ON

Both are dynamic variables and can be set on a running system like this:

mysql> SET GLOBAL slow_query_log=ON;
mysql> SET GLOBAL log_queries_not_using_indexes=ON;

Refer to https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html for full details and note that by default queries not using indexes are not logged to the slow query log, but if log_queries_not_using_indexes is enabled, ALL queries not using indexes will be logged, regardless of if they take longer than 'long_query_time' to execute.  If the number of queries logged is excessive, use log_throttle_queries_not_using_indexes to limit the number of queries not using indexes that will be logged per minute.  Like the other two global variables, this is dynamic and can be changed on a running system like this, e.g. to set it as 10 per minute:

mysql> SET GLOBAL log_throttle_queries_not_using_indexes=10;

That ought to fix you up.


Disable all constraints and table checks while restoring a dump I've obtained a dump of my PostgreSQL database with:

pg_dump -U user-name -d db-name -f dumpfile

which I then proceed to restore in another database with:

psql X -U postgres  -d db-name-b -f dumpfile

My problem is that the database contains referential constraints, checks and triggers and some of these (checks it would seem in particular) fail during restoration since the information is not loaded in the order that would cause those checks to be honored. For instance, inserting a row in a table may be associated with a CHECK that calls a plpgsql function that checks whether a condition holds in some other unrelated table. If that latter table is not loaded by psql before the former, an error occurs.

The following is a SSCCE that produces such a database that once dumped with pg_dump cannot be restored:

CREATE OR REPLACE FUNCTION fail_if_b_empty () RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

CREATE TABLE IF NOT EXISTS a (
     i              INTEGER                    NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);
CREATE TABLE IF NOT EXISTS b (
    i  INTEGER NOT NULL
);
INSERT INTO b(i) VALUES (0);

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty());

Is there a way to disable (from the command line) all such constraints during dump restoration and enable them back again afterwards? I am running PostgreSQL 9.1.

So you look up other tables in a CHECK constraint.
CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction in the manual:

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row.

Still, expressions in CHECK constraints are allowed to use functions, even user-defined functions. Those should be IMMUTABLE, but Postgres does not currently enforce this. According to this related discussion on pgsql-hackers, one reason is to allow references to the current time, which is not IMMUTABLE by nature.
But you are looking up rows of another table, which is completely in violation of how CHECK constraints are supposed to work. I am not surprised that pg_dump fails to provide for this.
Move your check in another table to a trigger (which is the right tool), and it should work with modern versions of Postgres.
PostgreSQL 9.2 or later
While the above is true for any version of Postgres, several tools have been introduced with Postgres 9.2 to help with your situation:
pg_dump option --exclude-table-data
A simple solution would be to dump the db without data for the violating table with:
--exclude-table-data=my_schema.my_tbl

Then append just the data for this table at the end of the dump with:
--data-only --table=my_schema.my_tbl

But complications with other constraints on the same table might ensue. There is an even better solution:
Solution: NOT VALID
Up to Postgres 9.1, the NOT VALID modifier was only available for FK constraints. This was extended to CHECK constraints in Postgres 9.2. The manual:

If the constraint is marked NOT VALID, the potentially-lengthy
initial check to verify that all rows in the table satisfy the
constraint is skipped. The constraint will still be enforced against
subsequent inserts or updates [...]

A plain Postgres dump file consists of three "sections":

pre_data
data
post-data

Postgres 9.2 also introduced an option to dump sections separately with -- section=sectionname, but that's not helping with the problem at hand.
Here is where it gets interesting. The manual:

Post-data items include definitions of indexes, triggers, rules, and
constraints other than validated check constraints. Pre-data items
include all other data definition items.

Bold emphasis mine.
You can change the offending CHECK constraint to NOT VALID, which moves the constraint to the post-data section. Drop and recreate:
ALTER TABLE a
  DROP CONSTRAINT a_constr_1
, ADD  CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID;

A single statement is fastest and rules out race conditions with concurrent transactions. (Two commands in a single transaction would work, too.)
This should solve your problem. You can even leave the constraint in that state, since that better reflects what it actually does: check new rows, but give no guarantees for existing data. There is nothing wrong with a NOT VALID check constraint. If you prefer, you can validate it later:
ALTER TABLE a VALIDATE CONSTRAINT a_constr_1;

But then you are back to the status quo ante.


Add banner motd to postgres database I would like to present a banner motd (message of the day) to users that connect to a Postgresql database. Similar to cisco router and switch cli where you use:

banner motd "Seien Sie bitte vorsichtig....."

And then upon logging into the switch/router you see that message.

I'd like to see something similar when connecting to a database.

example:

$ psql postgres
psql (12.0)
Type "help" for help.
Seien Sie bitte vorsichtig.....

postgres=#

You cannot force that on the server side, because the server cannot control what a client displays.

But yan can do it on the client side: create a .psqlrc file in your home directory that contains something like:

\echo Seien Sie bitte vorsichtig.....

But I assume, given the text of the warning, that you want this bannet only for certain sensitive databases.

For that, you could have  banner table in every database, and the statement in .psqlrc would be like:

\set QUIET on
\pset format unaligned
SELECT * FROM banner;
\pset format aligned
\set QUIET off


Another bad execution plan! This is running SQL Server 2012 SP2 Enterprise Edition.

I have a nightly ETL job that, for a specific SP, is taking way too long to run.  During my investigation I found that it was a specific update as part of the SP that is estimating a single row in its execution plan, hence the bad plan.

A bit of background on this SP, it does some ETL and loads into a table then 5 different update statements run as part of the SP and update this table, it's one of these updates that's causing the problem.

More annoyingly, this SP does have parameters but they aren't used anywhere in the actual SP. I am waiting for dev to remove these and obviously it needs fixing elsewhere.

To try and solve I have put some rebuild statistics in the SP after the ETL part, as during investigation doing this then rerunning the offending update statement returned a good plan but when this runs as part of the nightly ETL it keeps returning the bad plan (SQL Sentry monitor is paying for itself here!!), even with recompile, I have also tried creating a new SP and calling that after the update stats instead of the updates text, wondering if it was some sort of scope issue, but to no avail.

The frustrating part is that if I execute this SP manually all is ok, it runs in less that 60 seconds. If I leave this to run as part of ETL it takes roughly 3 hours to run.  I am at a loss as to why this is happening...  I am wondering if this is something to do with the way the SP is called as the devs have dynamically called the SP by using Execute T-SQL and then manually building the SP name?

If there is a particular plan you want it to use, regardless of the statistics, then try using a plan guide for that query. It seems pointless to try to persuade the optimiser when there's a known behaviour you want to use. It's better to update your stats AFTER your ETL, not just to persuade your ETL to run well.


Isolate database performance on an instance I have SQL Server 2014 Standard Edition installed on a machine with two different users and two catalogs, each of them accessed by a different user (each user is strictly tied to his catalog).

Is it possible that a time consuming operation on a catalog slows down operations on the other? How can I avoid that?

The physical server machine has four cores assigned, so I assume SQL Server can use more than one of them, but I have not configured anything explicitly.

You're question is very broad.  But in general the answer is yes.  A single instance has to share resources across all databases unless configured specifically not to.
By default, SQL uses all available CPU's for all operations.  You can configure MAXDOP (max degree of parallelism) on the database so a single query will only have access to that many CPU's.
IO is another tricky issue.  Generally it's best to seperate your workloads on physical hardware whenever possible.  Logs, data and tempdb all on seperate physical storage.  In addition, if you are trying to create a seperation of resources for the databases, you can use filegroups to assign the objects or the entire database to a separate storage device.
There are many, many other answers but again.  The question of 'how can I avoid that?'(in terms of cross database resource allocation) is way too broad for a specific answer.  If you are looking at one aspect, say IO, or CPU usage, it's an easier, more specific answer.


Can not drop a PostgreSQL database using dropdb? I can not drop a database using

dropdb

I get this error:

 ERROR:  must be owner of database

but with psql \l I see user admin (the one dropping the db) with

 admin=CTc/postgres

besides the database I want to drop.

What is wrong in PostgreSQL 9.x in Linux?

What you see there with psql \l only means that the Postgres role admin has been granted a couple of privileges by the Postgres role postgres - which is the default superuser and probably the actual owner of the database.

Quoting the manual:

  If a superuser chooses to issue a GRANT or REVOKE command, the command
  is performed as though it were issued by the owner of the affected
  object. In particular, privileges granted via such a command will
  appear to have been granted by the object owner.

The privileges granted are:

C -- CREATE
c -- CONNECT
T -- TEMPORARY

CONNECT and TEMPORARYon databases are also default default privileges for PUBLIC, but what we see here has been granted explicitly.

The privilege to drop the DB is not given, it cannot be. Only the owner of the database can drop it. (Or a superuser.) Hence the error message:

  ERROR:  must be owner of database

Typically, each shell user connects with a Postgres role of the same name. So, given you have the necessary sudo rights, this should do it:

sudo -u postgres dropdb my_database_name_here

Or switch to the  shell user postgres like demonstrated here.

But you may have to disconnect other users first. See:

Force drop db while others may be connected


DB2 sorting by lowercase uppercase - problem I'm struggling with DB2 - sorting by Polish letters with diacritics and uppercase/lowercase. When I created a database using:

CREATE DATABASE P1DS8 AUTOMATIC STORAGE YES ON '/home/db2inst1' DBPATH ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY PL COLLATE USING SYSTEM_912_PL PAGESIZE 32 K

Sorting by Polish letters works properly, however ascending sorting returns results ordered by lowercase as first aAbB... How I can change this setting to revert results to AaBb..?

Is the 'COLLATE' section is responsible for all aspects of sorting? (Specific language letters, lowercase, uppercase etc. )

You'd need to use the UCA (Unicode Collating Algorithm): the COLLATE value would be something like UCA500R1_LPO_CU for Polish and uppercase first. More info here.

Note that you cannot change the collating setting of an existing database; you would have to create a new one with the required collation and move your data.


Can I determine the service pack for all my SQL Servers using SCCM? I'm trying to figure out a way to find out all the service packs for my sql servers. There's about 30+ databases which we hosted & to login to each of the server and check the service pack will going to take some time. Can I use any tools e.g. sccm to crawl into all the sql servers and provide me with the sql versions & it's service packs?

Very quick and easy solution would be to setup a CMS (Central Management Server)
This enables you to run queries on groups of servers.
So if you make a production group, you can run a query on all of them at the same time
Using
SELECT @@VERSION

Would return you all the versions of the group.
If you're managing a bunch of different servers, a CMS is always a very helpful tool.


Compare two similar Postgres databases for differences I occasionally download publicly available data sets in the form of Postgres dBs.   These datasets are updated/modified/expanded over time by the repository host.

Is there a Postgres command or tool (ideally FOSS) that can show the differences between an OLD and NEW Postgres database ?  (A working assumption is that 95% of the entries are unchanged and that the tables and relationships also do not change).

Have a look at the Stack Overflow Q & A, How to check difference between two databases in PostgreSQL?; in particular Another PostgreSQL Diff Tool (apgdiff) (schema comparison only). Apgdiff is recommended by Alexander Kuznetsov - a published database book author and bit of a guru.

Maybe also look at Liquibase or Compare Database Table Data.

There are also commercial products - e.g. Altova Database Differencing with DiffDog


How the statement based replication works internal on secondary node of mongodb? Mongodb supports statement-based-replication.

Would like to know, how the statement based replication converts the code internally on secondary node of mongodb.

Is that working related to the mysql Row-based-replication ?

Mongodb do support only row based replication for the data durability among the master and slaves, when you hit a update command like below

db.collname.update({"empid":43123},{$set:{"age":{$inc:1}}})

the above statement search for the employee with employee id empid 43123 and increaments his age to 1, if his age is 26 it will make it as 27, this operation happens only in master, if you go and check the oplog for this statement the oplog entry will be like below

db.collname.update({"empid":43123},{$set:{"age":27}})

so mongodb replication always work on the basis of row which is updated.


Need best way to to avoid Replication Re-Configuration I need the best way for the scenario because of which I got irritated.
I do have 3 Production database Servers Srv1, Srv2 and Srv3.
I configure transactional replication among these servers.
Srv1 is Publisher and Srv2 and Srv3 are subscribers.

On production server everything running fine without any problem.

I have QA environment same as production replication configured.
On request of QA team, I need to refresh QA with production databases weekly (almost), which cause to fail replication in QA environment.

Because I have 1000 tables dropping and re-creating publications takes too much time on Srv1.

If I refresh database without dropping publication and recreate publication using backup script I get The process could not execute ‘sp_repldone/sp_replcounters’

Thanks

What @Kin said is correct but may be time-consuming (if you have really big tables involved in replication) when you need to run Snapshot.

Here are the steps that you can omit the snapshot

Script out QA replication and change @sync_type parameter value (highlighed
below)

exec sp_addsubscription @sync_type = 'Replication Support Only', ... (other paramaters should remain the same)
Remove replication (you can write a script to do this, note, first
remove subscriptions then publisher, but do NOT remove distributor)
Restore PROD db on QA publisher
Restore PROD db on QA subscribers.
Run the replication setup script on QA publisher
Test your replication by inserting/updating a few test records and see whether the replication works as expected.

This link talks about another ways that can avoid snapshot and may be of help (but I never tried myself)

https://blogs.msdn.microsoft.com/repltalk/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication/

1st step of scripting out QA replication is one time work and you can actually automate all these steps (after step 1 modification is done) via t-sql scripts, and using sqlcmd.exe or powershell to drive the t-sql scripts. I used this way frequently before.