Degree of parallelism and scheduler usage Is it ever possible for a SQL Server query running with an effective degree of parallelism x to have parallel workers assigned to more than x distinct schedulers? Even if the execution plan has many parallel zones?

Up to SQL Server 2000 yes; in SQL Server 2005 and later, no.
According to Craig Freedman's Parallel Query Execution Presentation, up to SQL Server 2000, any parallel worker thread could run on any CPU. That means there may be more active threads than DOP, so reducing DOP might not actually reduce CPU usage. To be clear, a single parallel query might indeed use more than x distinct schedulers if it had more than one parallel branch/zone in the execution plan.
The behaviour was changed from SQL Server 2005 onward so that the number of schedulers per query is limited to DOP. That means a single parallel query never uses more than DOP CPUs, and therefore reducing DOP does reduce CPU usage. This applies regardless of the number of parallel zones (branches) in the execution plan.
My understanding is that the pre-2005 behaviour was an implementation bug/oversight.

Note: The additional parallel worker threads are limited to DOP schedulers; e.g. a parallel query that runs at DOP 4 on an instance with 8 schedulers available would use 4 schedulers for the parallel workers. The query as a whole could use 5 schedulers: 1 provided by the session plus the 4 for the additional parallel workers.


How to store a sequence of events related to a transaction I am using Sql Server 2008 R2.

I have two tables that describe import processes and export processes.
Import table has these columns with others that just have details about each type of import

ImportID, ImportFileFormat, ImportFileLocation,...

The Export tbl is similar to the import table except it describes outputs and other export specific data.

ExportID, ExportFileFormat, ExportFileLocation,...

Now I need some way of storing a 'Transaction' and I am stuck on how to do this exactly.

A 'transaction' is any number of imports and exports in any unique sequence.  Eg: T = I1, E1, E2 or I2, E1, E3, I1 or I1 or E3 or any unique combination of importing / exporting with no limit to number of Imports/ exports.

A 'transaction' may eventually include other 'eventtypes' other than just import/export like 'run_sp' or some sort of action in the database or application.

How might I go about doing something like this? I have looked at what I think may be a similar question on stackoverflow. This seems like something that may do what I want but I am unsure.

Any help would be greatly appreciated.

Edit #1:

tblTransactions:

TransactionID   TransactionNumber   EventTable   EventID
1               1                   'tblImport'  1
2               1                   'tblExport'  3
3               2                   'tblImport'  1
4               3                   'tblExport'  3
5               2                   'tblExport'  2

Why not just have a transactions table:

CREATE TABLE dbo.Transactions(TransactionID INT IDENTITY(1,1));

When you're about to start a new transaction (a set of imports and exports), do this:

DECLARE @tID INT;
INSERT dbo.Transactions DEFAULT VALUES;
SELECT @tID = SCOPE_IDENTITY();

Now in your import and export tables, add a column that holds the transaction id, and store @tID there for every action in this sequence.

Or use a separate table to store Transaction + Event combinations, if events can be recycled. For example:

CREATE TABLE dbo.TransactionEvents
(
  TransactionID INT,   -- foreign key
  EventTypeID TINYINT, -- 1 = import, 2 = export
  EventID INT,         -- implicit foreign key to import/export table
  ... other columns ...
);

You can have a lookup table for EventTypes if you want, e.g.:

CREATE TABLE dbo.EventTypes
(
  EventTypeID TINYINT PRIMARY KEY,
  Name SYSNAME UNIQUE
);
INSERT dbo.EventTypes VALUES(1,N'Import'),(2,N'Export');

You might also consider not having separate import and export tables, but one events table with a type of import or export. Then the foreign key here can be explicit.

And please ditch the tbl prefixes. All they do is make you type three extra characters every single time.


Difference between table fillfactor and index fillfactor In Postgres, one can set fillfactor for an index as well as a table. What is the difference? How to decide values for either. What are the usecases?

I am trying to cluster a spatial relation on a spatial index. It has a couple of million records. The records are updated all the time though very few new ones are created daily.

The use case is spatial range queries.
What will be a good value for the table fillfactor and / or the index fillfactor?

From the CREATE TABLE manual page (emphasis added):

  The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate.

From the CREATE INDEX manual page (emphasis added):

  The fillfactor for an index is a percentage that determines how full
  the index method will try to pack index pages. For B-trees, leaf pages
  are filled to this percentage during initial index build, and also
  when extending the index at the right (largest key values). If pages
  subsequently become completely full, they will be split, leading to
  gradual degradation in the index's efficiency. B-trees use a default
  fillfactor of 90, but any value from 10 to 100 can be selected. If the
  table is static then fillfactor 100 is best to minimize the index's
  physical size, but for heavily updated tables a smaller fillfactor is
  better to minimize the need for page splits. The other index methods
  use fillfactor in different but roughly analogous ways; the default
  fillfactor varies between methods.


moving all databases from SQL 2005 EXPRESS SP2 to SQL2005 Enterprise SP4 Thank you for such a great forums on web.
I'm not a DBA specialist and my knowledge about SQL server is low so this simple issue is getting challenging for me.

We have an old SQL Server express 2005 SP2 on WINDOWS 2003 platform.

We want to copy all of the databases to our new server which is 2008R2 and it is suggested to have SQL Server SP4 to have compatibility on 2008R2

My question is this:

How to move/copy all of the credentials and databases and the whole data from OLD server to new server?

We can have 4-5 hours of downtime and i have only 100 databases.

I want to use the easiest way and the must simple procedure to do this job.

** A friend of mine told me SQL Server management studio 2012 has a built in COPY DATABASE wizard which easily copy data from source to destination server. But it needs SQL Agent be installed on the source server. Since the source server is EXPRESS edition so we don't have any AGENT there.

Do you have any easy to apply solution? I will be thankful if you guide me in details since i'm not a DBA specialist and my main work area is on other web services expect MSSQL.

I have also installed SP2 on the new 2008R2 server and i didn't find any issue with that.

Thank you for your time on my case.

Since you have downtime, best is to use this powershell script- with backup restore option

This script will take care of moving databases, logins, jobs,etc on the new server. make sure to use backup restore switch as opposed to detach/attach.


Excluding Read only databases using Ola Hallengren script Is there a way to exclude Standby or Read only databases from Index maintenance and also from backups using Ola Hallengren scripts.

It's available for backups.

Though it's a somewhat recent addition, so you may need to upgrade your scripts.

For index maintenance, database status is checked so commands don't actually run on databases that aren't fully accessible.

IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'
BEGIN

Hope this helps!


Find all version of SQL Server on a network? There seems to be a bit of SQL Server sprawl at a client and I'd like to document all of them.

What is the best way to find all installed versions of SQL Server on a network?

SQLPing should do the job

I've used it on medium and small networks and it seemed to find all stray SQL Server installs


Multi-Datacenter design for SQL Server I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different continents, something like building a SaaS over your private "cloud".

Imagine you have several offices in LATAM, EMEA, EAST and EEUU. Each office in every region has its own SQL Server with local databases and you are tired of managing hundreds of servers.

Easiest way to do resolve this is converging all small databases into a big one, placing it into a datacenter. As a first approach, I though that a single datacenter based in a single region will be enough to satisfy our requirements, but it won't because of latency. I mean that local users in one region can't work with a database based in a different region.

What's next? Simple thing. Deploy a datacenter in every region you want and every database will contain the same information with read/write and HA capabilities. At this point, I guess SQL Server with an AlwaysOn cluster will satisfy HA, but what about read/write? Do I have to use any kind of replica with every database service? Will it work?

And going further, imagine that a single country in a region needs to have its own database. How do I set up replication for this? What if I have to change a database schema?

Any help or clarification will be appreciated.

Our goal is to design a 3-tier application based on .net WCF published via Citrix XenApp. Due to some requirements this application won't work if the client is trying to connect to a far datacenter (i.e. LATAM to EU) because of latency (WCF works with local devices). Resuming this part, we need to build 2 DC in every continent to offer a reliable application tier with a proper fault tolerance and HA. Our main problem is to know what can we do at data tier...

My first idea was to deploy a SQL AlwaysOn cluster in the primary datacenter as a central node, with N SQL Std replicating bidirectionally from this master node. Yes, I said bidirectionally because some people at my work are fully confident with this scenario, but I'm still trying to understand how it will work in SQL Server (even if it's possible). I have more experience with MySQL and Galera and it works, but my SQL Server knowledge is limited.

I know you will ask me, why don't you connect directly from application tier to this central database? Perhaps this is the best choice because this SQL service won't be quite exhaustive (100.000 transaction per day), but we have to ensure the service and application functionality at 99,99999.

At this point, I guess SQL Server with an AlwaysOn cluster will
  satisfy HA, but what about read/write?

I think this SQL Server 2012 whitepaper will be a good read for you at this point to get a feel for what's possible with the two different features (Availability Groups and Fail-over Clustered Instances) that come under the AlwaysOn brand name.

  Deploy a datacenter in every region you want and every database will
  contain the same information with read/write and HA capabilities

You ask about the read/write capabilities and I think this is where you will be disappointed. Even with a combination of local FCIs with a cross site AG it is only ever possible to have one node as the PRIMARY (writable) node. The other nodes can act as read-only nodes to allow you to off load reads and backups.

From my experience with building systems like this you really want to keep things as simple as possible. For this reason I wouldn't recommend trying to extend an AlwaysOn FCI + AG to more than 2 sites. I would also stay clear of bidirectional transnational replication as this has a tendency to break.

Without more information it is difficult to recommend a solution for you. Some very general topics to consider would be; a caching layer in your app at each site to help with the latency or a SQL Server Azure deployment.

After the update to the question:

I really wouldn't recommend you to use SQL Server in that way. Bidirectional replication between 2 nodes is bad enough. >2 nodes plus the cost of all those Enterprise Editions of SQL Server just sounds like a bad idea. Maybe take a look at what Azure has to offer.


How to get performance benefits from a view vs subquery? I'd like to know how/if anyone has gained significant performance benefits from using views instead of subqueries.

I would prefer not to define a specific case, since I'm trying to establish good practice and rule of thumb in addition to case-by-case.

An example would be finding the last claim date in an insurance policy claim list where you started the search by sorted/filtered customer set and all claims are in their own table.
I'm currently using a view and thinking about a subquery instead.

Things that might affect performance across cases:

Can views be used somehow to avoid a full scan where a subquery would need to?
Are there any limitations/caveats on ensuring that the best indexes are used when joining to a view?

The case where you can achieve performance benefit using a view (or common table expression = "inline view") instead of a subquery is if you have to repeat the same subquery several times in your query.

If instead you can replace each subquery with the same view name (or named common table expression) then the optimizer knows it's the same thing and can either cache the result or restructure the execution path so that the result only needs to be queried once.


Understanding the flaw in the query Suppose we have a relation

Employee={empId,name,department, salary}

and we are interested in finding the employees who get more salary than anyone in department 5. Would the following query work?

Select e.empId
from employee e
where not exists (select * from employee s where s.department = 5 and s.salary >= e.salary)

Can this fail in any case? If so how?

It looks fine to me. It correctly avoids listing any employees from department five, even if there are multiple employees with the same highest salary in that department.


Update column value based on other column with manage duplicate value MySQL query I need to update column value based on other column with adding separator and with manage duplicates values using sql query.

 For example :

 I have one database table A.

Here in it two columns :
1) locality
2) slug

Locality column already have data.
So need to update 'slug' column based on locality value.
Please review below screenshots.

Currently i have below table.

And i need to update 'slug' column like below screenshot.

Assuming the suburb_id is autoincremental primary key:

WITH cte AS ( SELECT suburb_id,
                     ROW_NUMBER() OVER ( PARTITION BY LOWER(locality)
                                         ORDER BY suburb_id ASC ) rn
              FROM tableA )
UPDATE tableA, cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN LOWER(tableA.locality)
                       ELSE CONCAT( LOWER(tableA.locality), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

  > are you sure that WITH ... UPDATE ... has been implemented in MariaDB? – ypercubeᵀᴹ

If not, then simply convert CTE to subquery:

UPDATE tableA,
       ( SELECT suburb_id,
         ROW_NUMBER() OVER ( PARTITION BY LOWER(locality)
                             ORDER BY suburb_id ASC ) rn
         FROM tableA ) cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN LOWER(tableA.locality)
                       ELSE CONCAT( LOWER(tableA.locality), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id


High WRITELOG wait on Azure SQL Database My WaitStats analysis of our Azure SQL Database is showing WRITELOG and HADR_SYNC_COMMIT as counter the highest wait. This is a Premium 250 eDTU resource.
Script
SELECT   TOP (10) wait_type,
                  CAST (([wait_time_ms] / 1000.0) AS DECIMAL (16, 2)) AS [WaitS],
                  CAST (100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS DECIMAL (16, 2)) AS [Percentage]
FROM     sys.dm_db_wait_stats
ORDER BY [Percentage] DESC;

I cannot find much information on how to address this in AZure SQL database.
Any help is appreciated.
Thanks

WRITELOG is waiting on commit for your transaction's log records to be hardened to disk, and HADR_SYNC_COMMIT is waiting on commit for your transaction's log records to be sent over the network to a secondary replica and hardened to disk.  So they are very, very similar waits.
The both indicate that your application is commiting a lot of transactions, perhaps too many.
And on premium your log file is on a local flash drive with very low latency, so to suffer lots of WRITELOG waits suggests there's something that needs to be fixed in your application.
If you have any processes that run INSERT, UPDATE, or DELETE of single rows in a tight loop, consider wrapping them in an explicit transaction, so you only have to wait for the transaction log to be flushed at the end.
As always Query Store is your friend, and can show you the waits by query, and you can analyze the waits by session too in sys.dm_exec_session_wait_stats to see what parts of your workload are suffering these waits.
You can get a better sense of how much your clients are waiting by comparing the session elapsed time and cpu time to the wait times.  EG
select s.session_id,
       w.wait_type,
       w.wait_time_ms,
       w.signal_wait_time_ms,
       s.total_elapsed_time,
       s.cpu_time,
       w.wait_time_ms/cast(nullif(s.total_elapsed_time,0) as float) wait_percent_of_elapsed
from  sys.dm_exec_sessions s
join sys.dm_exec_session_wait_stats w
  on s.session_id = w.session_id
where w.wait_time_ms > 0
order by wait_percent_of_elapsed desc


In a one-to-one relationship, where should the foreign key be placed? I am currently designing an ERD for a project management system. I am unable to choose between two workable solutions as the best solution as I wish to embrace the best standards.

The two entities that can access the system are the clients and employees. Since they have different information, I decided to treat them as different entities. I figured that it is best to have a single table for users of the system rather having the username and password fields be found in both tables of clients and employees. Plus, the constraints I have to follow:

All users are only limited to employees and clients.
But not all employees are required to be users of the system.

Here are the following entities without relationship yet defined:

Now, there is only one-to-one relationship between users and clients, employees and users. My question is where should I place the foreign key? Should I place two foreign keys in users table namely employee_id and client_id which makes it easier to find the associated client and employee entity from the user entity and where I can define authorization rules more easily? Or should I place each foreign key on clients and employees referencing the users table since I am unsure if it is an acceptable practice to have two foreign keys in a table in which only one foreign key can be used at a time.

It is acceptable to have mutually exclusive attributes in a table.  If you only have one pair of such attributes, this may be the most practical solution.

However, some people may look at your situation as a sub-typing issue.  In this view, you are missing the superset entity.  You have a collection of (something), some of which are employees and some of which are customers and some of which are also users.

Have you also considered what happens if an employee also happens to be a customer?  Is that important to your system or is that something that would be considered coincidental?

If you think its important to treat all users as a feature of one type of thing, then you need to implement the (something) which is currently missing from your table definitions.

Consider the following ERD:

If you supply the missing superset entity, then every employee is one of these, as is every customer, and also therefore every user.  The hard part of this is coming up with a reasonable name for this superset, but it solves the issue of having to figure out which foreign key to follow from user.  It also makes a connection between employee and customer should one happen to exist, and should that connection be of interest.


What does MariaDB do at midnight? Does MariaDB do anything in particular at midnight and lasts for 1.5 hrs?

I've noticed this over the past week that mysqld uses 100% CPU and 75% RAM usage slowing down the rest of my applications.

Looking at the current connections there is no slow queries or similar happening at this time.

This is 50GB InnoDB database with >2500 tables

What can I do to troubleshoot what is going on at this time of day? The rest of my application grinds to halt and critical processes cannot work fast enough as mysqld hogs the resources.

Thanks,

Hi user1202278 (is that your real name?),

MariaDB does not perform any particular scheduled actions on its own.  There is almost certainly a cron job at work, unless there are users who stay up late every night!

It would be a good exercise to monitor your DB for a while and see how often and when heavy processes are running.  From there, you can narrow down the suspects.  If you haven't already, I would recommend that you invest a good monitoring tool.  I personally use Navicat Monitor for MySQL/MariaDB.  It provides all of the important stats on the Dashboard page and can track server metrics such as disk usage, I/O, CPU, RAM, etc.

It has a 14 day trial, which should be plenty of time to diagnose your issue.

Best of luck!


Unable to add collumn in mongo Something strange in this neighbourhood. Calling DBA's and ! GB
db.getCollection('contacts').update({}, {$set: {"source": "domain.txt"}},false,true);

It failed with:
admin> db.getCollection('contacts').update({}, {$set: {"source": "domain.txt"}},false,true);
[2020-09-29 23:24:32] java.lang.IllegalArgumentException: options should be an object: false
[2020-09-29 23:24:32] options should be an object: false

Also, table feels a little corrupted. No longer displays the amount of records.
Based on answers from https://stackoverflow.com/questions/7714216/add-new-field-to-every-document-in-a-mongodb-collection

Turns out mongo cli client needs to be on version 4.4
I had version 3


Order by numbers followed by alphabet and then null values I have below column in my database:

abc
1
2
null
100
6

I want to sort and show these values as per below sequence:

1
2
6
100
abc
null

Anyone please help me to solve it.
Consider below query:

select * from test_table where status=1 order by test_column DESC

OR

 select * from test_table where status=1 order by if((test_column = '' OR test_column IS NULL),'999999',test_column) DESC

select
*
from
t
order by
case
when col regexp '^[0-9]' then 1
when col regexp '^[a-zA-Z]' then 2
when col = '' or col is null then 3
end
, col * 1 /*this converts to number, so that 100 is not sorted before 2*/
, col /*finally sort strings correctly*/


TSQL Extract first word as First name. If only one word or null, set to ‘’ Table column has values it includes prefix as well. I want to extract first word as First_name. If only one word or null, set to ''.

Name
----------
Abhijit K
Ravi Mahta
Dr.Rajiv
Dr sharma
Mr.Raju
Miss Anjali

Output I want:

First_name
----------
Abhijit
Ravi
Rajiv
sharma
Raju
Anjali

This is a little different than what your stated goals were, but it might be what you want.

with cte as (
  select Prefix
  from (values ('Dr '),('Mr '),('Miss ')) v (Prefix)
)
select
    prefix = case when left(s.Name,charindex(' ',s.Name+' ')) in (select Prefix from cte)
          then left(s.Name,charindex(' ',s.Name+' '))
        end
  , first_name = case when left(s.Name,charindex(' ',s.Name+' ')) in (select Prefix from cte)
          then stuff(s.Name,1,charindex(' ',s.Name+' '),'')
        else  nullif(left(s.Name,charindex(' ',s.Name+' ')),'')
        end
from (
  select name = replace(Name,'.',' ')
  from t
    ) s

rextester demo: http://rextester.com/BQQO51384

returns:

+--------+------------+
| prefix | first_name |
+--------+------------+
| NULL   | Abhijit    |
| NULL   | Ravi       |
| Dr     | Rajiv      |
| Dr     | sharma     |
| Mr     | Raju       |
| Miss   | Anjali     |
+--------+------------+


How to export mysql databases with different ENGINE? I'm setting up MySQL Cluster. As it uses ENGINE=NDBCLUSTER, how could I export my databases from MySQL Server specifying the ENGINE?

Is there a way to do this?

All my tables are MYISAM

You should create two mysqldumps

Schema Only, No Data
Data Only, No Schema

Using sed, mysqldump all databases except information_schema, mysql, performance_schema

Create a script with the following commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DB_CSV=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
DBLIST=`echo "${DB_CSV}" | sed 's/,/ /g'`
#
# Schema Only, No Data
#
mysqldump ${MYSQL_CONN} -d -B ${DBLIST}|sed 's/ENGINE=MyISAM/ENGINE=NDBCLUSTER' > schema_only.sql
#
# Data Only, No Schema
#
mysqldump ${MYSQL_CONN} -t -B ${DBLIST} > data_only.sql

Run this script. You should have two files

schema_only.sql

Should have all the CREATE TABLE commands
Should have no data
All ENGINE=MyISAM will now say ENGINE=NDBCLUSTER

data_only.sql : All the INSERTs to reload the tables

Run the schema_only.sql` script in the MySQL NDB Cluster setup.

Check to make sure every table was created.

Make sure there are now special options prevent the table creation, such as

FULLTEXT indexes
ROW_FORMAT option

If something went wrong, you can always hand edit schema_only.sql and reload it.

Once every table is in place, run data_only.sql in the MySQL NDB Cluster.

Give it a Try !!!


SQL Server Cache population on server / service startup We have upgraded our SQL Server instance with buckets of RAM, so it can now much more effectively cache . The issue is that it takes time (days) to populate the cache. As far as I understand this is something internally managed by the engine, based on user queries. Is there value in pre-empting this process by running a series of select * statements on big / frequently used tables to cache all this data on service start up? Any particularly clever way to do it?

There isn't normally any added value in doing this: you may cache data that isn't needed that requires eviction when your real load occurs.

How much time do you think this will save too? Have you done some measurements to see if running a SELECT * on some table improves response after server start up?


MySQL connection string using password I am creating app with ExpressJS and want to use MySQL with password, but I have problem with connection string

mysql://user@localhost:3306/db

in this string where to specify password to connect to db

Please help me

You looking for Uniform Resource Identifier (URI) scheme, and the syntax is:
mysql://username:password@host:port/database/tablename

See the MySQL documentation


PostgreSQL requirements for multi workstations Can PostgreSQL run on network environment?  Does it required a dedicate machine to host the PostgreSQL engine, like MS SQL?

What am I trying to do is having multiple workstations can access to the network database at the same time, without connect to dedicated host machine.  The MS SQL Express doesn't allow me to put the database to network drive.

Yes, PostgreSQL is fully network aware. You can put it on a separate machine or not, this all depends on how you use it, so you will need to state your requirements, type and volume of data, etc.

See for example https://www.postgresql.org/docs/current/static/runtime-config-connection.html and https://www.postgresql.org/docs/current/static/ssl-tcp.html

For development purposes, you can install it on your own machine to develop your programs and later just move your application to use the production database.


Where can I found a example database for apply data quality improvement? I am doing a little database improvement project, but I need a large database example.

here we have some big public datasets I want to work with the million song Dataset some day. Also google offers some public datasets

Enjoy them!!


Unable to trace the transaction log space im new to sql server. One of the instance encounters a scenario it shows the transaction log full, and the database need to perform backup in order to solve the issue.

I want to replicate the scenario again to show there is potential that the transaction log would go full, and suggest my client to increase the transaction log space and grow factor. (The database is using Full recovery mode)

However, the scenario is unable to replicate whatever how demanding query executed (40000 deletion and then 40000 insertion, repeatedly), i found that the transaction log occupied space is just trembling by querying it

DBCC SQLPERF(LOGSPACE)

I expects the transaction log will grow continuously when executing bulk insertion and deletion (no backup or checkpoint is done between the query). But the fact is the log space isn't really behaved as expected.

I would like to know

when the db throw exception transaction log is full - is it due to the out of memory of harddisk storage? or is it due to log size is over the configured size?
why the operations of bulk insertion & deletion didn't reduce the log space, but its size behaves trembling(up and down in stead of continuous increase)?

why the operations of bulk insertion & deletion didn't reduce the log space, but its size behaves trembling(up and down in stead of continuous increase)?

It's likely that you've missed one of these two steps in your test setup:

the test database is not using the FULL recovery model

ALTER DATABASE [TestDB] SET RECOVERY FULL WITH NO_WAIT;

you have not performed a FULL backup of the test database

BACKUP DATABASE  [TestDB] TO DISK = N'NUL';

Until you've taken a full backup, even if the database is set to use the full recovery model, it's still essentially using the Simple model.  Here's a post where Paul Randal discusses the situation:

Misconceptions around the log and log backups: how to convince yourself

  If you switch recovery models to FULL or BULK_LOGGED, until you take the first full backup, you are still essentially in the SIMPLE recovery model, and so the log will truncate on checkpoint.

Since your question says "checkpoint is done between the query)", this second point is likely your issue.

To answer your other question:

  when the db throw exception transaction log is full - is it due to the out of memory of harddisk storage? or is it due to log size is over the configured size?

See this related question on this site for a lot of details on that topic: Why Does the Transaction Log Keep Growing or Run Out of Space?

It could be that the disk is out of space, or the log can't grow because it's reached it's max size and is unable to grow anymore.  Some solutions to this issue would be to take log backups more frequently, change to a different recovery model, increase the size of the disk where the log file resides, etc.'

Regarding a couple of comments on this answer:

  sorry, i missed one important question, you are correct that actually my database was not in full recovery mode, but in the SSMS UI, it did show the db is in full recovery mode. in this case, where can i check the actually recovery mode?

and

  Is there a way to see if the recovery mode is fully active or not yet because of missing backup (besides query last backup date). And does it count to have a full backup before switching to full recovery mode?

The only way I know of to tell if a backup hasn't been performed since the DB was switch to full is to attempt a log backup.  It will fail with the error "BACKUP LOG cannot be performed because there is no current database backup."

This really shouldn't be a problem in practice, since you're backing up real production databases, right? =P  But in a test environment, I would just run this to see where things stand:

BACKUP LOG NCEB_DMS TO DISK = N'NUL';


Add jsonb key/value pair with uuid I am trying to add a new key value pair to a jsonb array and I am having some difficulties in doing it.
What I am trying to add is a key id with a uuid value
This is how my data looks right now:
{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

And this is the result I am looking to achieve:
{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "id": "6a1d15ad-468b-4714-81f8-dccffdea3021",
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "id": "8099c2cf-2ff1-4855-9b54-aa0e62d4bfa5",
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

So far I've only managed to add the id key with a static value using this query
select  jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                jsonb '3',
                                true
                            ), imageElements
                    )
            )
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;

My question is, how can I generate a uuid for each key I am trying to add? Instead of my current static value 3 I want to add a uuid that must not be the same for all the elements in the array

I managed to solve this problem using this query:
select to_jsonb(array_agg(jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                to_jsonb(uuid_generate_v4()),
                                true
                            ), imageElements
                    )
            )))
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;


Sort by ID when ID < 1000, otherwise sort by name I have a table of Ids and Names. Where the Id is less than 1000, I want to order by Id. Otherwise sort by Names. For example, if I have:
Id   Name
2    A
1    B
3    C
1111 Z
2222 Y
3333 X

The result should be (first three rows sorted by Id, last three rows sorted by Name):
Id   Name
1    B
2    A
3    C
3333 A
1111 B
2222 C

I came up with this:
SELECT *
FROM table1
ORDER BY
  CASE Id
    WHEN Id < 1000 THEN
        Id
    ELSE
        Name
  END
  ASC
;

Which isn't really working - for the following table:
Id   Name
1    M
2    P
3    A1
4    C
5    A2
6    T
1009 A2
1011 A1
1010 A3

I get the following output:
1    M
3    A1
1011 A1
5    A2
1009 A2
1010 A3
4    C
2    P
6    T

What's happening here? And what's the correct query I'm after?

@ypercubeᵀᴹ has the correct answer in a comment.
To have this always be correct -- including cases when the names start with digits -- you need to have a two column order by clause as follows:
SELECT *
FROM table1
ORDER BY
  CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
  Name;

Observe that all Id's above 1000 are assigned the same value so that then the second column comes into play.


You can use the PIVOT operator, like this:

SELECT
   QID, ISNULL([1],0) AS [Type(1)], ISNULL([2],0) AS [Type(2)], ISNULL([3],0) AS [Type(3)]
FROM
   (SELECT QID, Type, COUNT(*) AS row_count
   FROM MyTable
   GROUP BY QID, Type) AS t
PIVOT
   (SUM([row_count]) FOR Type IN ([1],[2],[3])) AS PivotTable;

BOL article about using PIVOT and UNPIVOT.

SQL Fiddle with demo


Use of IMPERSONATE permissions in SQL Server? During some reading, got to know about the Impersonate permissions. From what I've read, it is more like creating a copy of the user with all the permission levels under a different name. I understand that this can be used for executing any queries under a different login but ultimately what purpose does it serve ?

Why was this feature introduced?

Personally I use impersonation for three major categories of tasks.

Testing
If I need to test what access someone has I can impersonate them, try out the task, and see if it works.  This is particularly useful when I've granted permissions but the user is still telling me they can't perform a given task.

Collecting information
There are a number of system views/functions that give you information about the connecting principals permissions (even some AD information).  As an example if I impersonate a database principal (user) and query sys.user_token I can get a list of all AD groups they are a member of and which ones give them access to the current database.

Granting access to a task without granting the permission
A specific example here is the ability to truncate a table.  In order to truncate a table you have to have the ALTER permission on the table.  I want to let some truncate that table but I don't want to risk them making changes to it.

Create a database principal (user) that has alter on the table
Create a stored procedure that does the TRUNCATE and uses EXECUTE AS to cause the SP to run as the user I created.
Grant execute permissions execute to the stored procedure.

You can even use this technique to grant sysadmin level permissions although it does have it's own difficulties and risks.

Edit:

I can see two possible reasons why you might grant someone impersonate rights.

Separate application permissions from direct access permissions

ApplicationA requires that user Joe have access to read from any table.  But as part of Joe's responsibilities he also needs to update a status table in case something needs to be re-written.  By granting update permissions to user UpdatePerms and granting Joe impersonate access to it he can log into SSMS, impersonate that user and update the table.  This means he has no update access through the application but can still perform this occasional task.

Require extra thought/action before performing a task

Similar to above.  Joe needs to be able to delete rows from a table, but you don't want him to do it by accident (or at least make it harder).  By requiring he impersonate another user before performing the delete he at least has to think about it a bit harder making it less likely to happen by mistake.

Note: I've never had to do either of these in production.  It just seems like logical possibilities.


How to configure global charset on MySQL I tried to change the charset for our MySQL via my.cnf and it failed.  I added the charset settings listed below:

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# ------------- MYSQL CHARSETS --------------
character_set_system = utf8
character_set_server = utf8
character_set_results = utf8
character_set_database = utf8
character_set_connection = utf8
character_set_client = utf8

collation_connection = utf8_general_ci
collation_database = utf8_general_ci
collation_server = utf8_general_ci

When I had these enabled, MySQL failed to start.  How can I make sure everything defaults to utf8 - always?

Update

There are still a few places having issues in being UTF8

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci

Unfortunately I still haven't come up with a soluton... any ideas?

Currently I'm using this configuration successfully:

[mysqld]
default-character-set = utf8
init-connect='SET NAMES utf8'
character-set-server = utf8

[mysql]
default-character-set = utf8

But PHPMyAdmin is still showing some latin1

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci

You need to incorporate the following into your my.cnf file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set = utf8
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

You can find more information on the following manual pages

http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_system
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_server

In response to Mark's answer, if you need to convert existing tables/databases to the correct character set you will need a suitable ALTER query. Something similar to the below:

ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Alternatively there's a great PHP script here which does it for you.


How to upload 1.5GB sql file on mysql? Whenever I tried to import file in MySql on my Linux Mint system, "You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit." this message shows. May anybody please help me that how to import 1.5GB. Thanks

You don't say how you are trying to process the file (command line, via a web based tool, ...) but I suspect that you are using something like phpMyAdmin's web based interface which is the most common way people interact with mySQL (aside from through their own code) in my experience.

Here you will run into issues with both PHP and the web server it is running within having limits on the size of any single upload. These limits are in place to prevent DoS issues (accidental or otherwise) due to /tmp or other areas becoming full so unless you run you own server you probably won't be able to get the limits pushed up high enough to deal with that large a file. If you do run your own server, see pages like this answer for details of how to make the required changes.

Other than this you have four options:

Split the file into smaller chunks and process them manually. This could be long winded for such a large file.
If you have shell access, upload the large file to the server and use the command-line mysql tools to import it (unfortunately I don't think phpMyAdmin has the feature to process a "local" file like this otherwise you could upload and then still use that).
Write your own simple PHP script (or other language depending on what is on your server) that reads the uploaded file and runs it. For smaller files loading the file into a string and calling multi_query() should do the trick, but you will run into memory limit issues with a 1.5Gb file so you'll need to be more clever than that (parsing the file and submitting individual statements or groups of statements instead of it all in one go).
Find an existing a tool that already does option 3b (reading a file from the web server's filesystem and processes it) for you.


Agent Job Running as Computer Domain Account Rather than Service account I have two SQL Servers as follows

Microsoft SQL Server 2016 (RTM-CU9-GDR) (KB4058559) - 13.0.2218.0 (X64)   Dec 27 2017 17:10:01   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)   Jul 20 2019 21:42:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Both are running the SQL Server Agent service as NT Service\SQLSERVERAGENT

If I create a test job as follows

USE [msdb]
GO

/****** Object:  Job [TEST]    Script Date: 13/08/2019 14:47:44 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 13/08/2019 14:47:44 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TEST',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [a]    Script Date: 13/08/2019 14:47:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'a',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'WAITFOR DELAY ''00:10:00''',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

And run it on the 2014 server, I can see using sp_whoisactive that the job is running as NT SERVICE\SQLSERVERAGENT

If I run it on the 2016 server, I can see using sp_whoisactive that the job is running as DOMAIN\SERVERNAME$(Which incidentally is not a login on the SQL Server)

Why is this behaviour different between the two servers? As the job is owned by sa, I would expect it to run as the service account user (which is what is happening on the 2014 server) but this is not happening on the 2016 server?

What is causing this?

The job is running as the same identity in both cases.  This is a difference only in how the information is reported.  NT SERVICE\SQLSERVERAGENT is a least-privilege local identity used for running SQL Agent jobs.  Like other built-in local Windows identities this account can access network resources using the computer account.  So from the local point-of-view Agent is running as NT SERVICE\SQLSERVERAGENT, but from the network point-of-view it's running as the computer account DOMAIN\SERVERNAME$.

I don't know why the difference in reporting between your servers, but it's nothing to worry about.

It looks like sp_whoisactive might get the login name directly from sys.dm_exec_sessions, which returns the domain identity of the login.  If you use the security_id to look up the session's server_principal you'll see the local identities differentiated.  eg:

select s.session_id, p.name
from sys.dm_exec_sessions s
join sys.server_principals p
  on s.security_id = p.sid


Support ticket system - when/how to move old tickets? I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting resolved. Obviously, if I tried to keep them all in one table ever since the beginning, the table would become HUGE.

My questions are:

Once a ticket gets resolved, should I move it out of the original table; or
Should I just move resolved tickets out of the original table after they are 90 months old; or
Should I move everything whether resolved or unresolved into another table after 90 months and do some kind of UNION every time unresolved queries are sought?

What is the problem with the table becoming large?  Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant.  The cost of using an index will grow at an O(log(n)) rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table.  And you can potentially limit that further by using function-based indexes to limit the size of the index by doing things like only indexing the active rows.

The only queries that should care about the size of the table are queries where you want to do a full scan on the table in order to do things like produce metrics about how many tickets have been opened since the beginning of time (or, at least, over a significant fraction of history).  If you are concerned about those sorts of reporting queries, you can do things like use materialized views to pre-aggregate the data.

Normally, I would suggest keeping a single table and ensuring that appropriate indexes and/or materialized views exist to support the queries about whose performance you are concerned.


Timeout expires during database restore I'm using this query to restore a database from a C++ program

RAISERROR ('-- Performing full restore...', 0, 1) WITH NOWAIT
USE MASTER;
ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [{0}] FROM  DISK = N'{1}'
WITH  FILE = 1, REPLACE, KEEP_REPLICATION, NOUNLOAD, STATS = 5;
ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
RAISERROR ('-- Performing full restore: Done', 0, 1) WITH NOWAIT;

On my computer it works as intended, but when I try to restore the database on our customer's computer an error message pops out saying

  Timeout expired.  The timeout period elapsed prior to completion of
  the operation or the server is not responding. The backup or restore
  was aborted.

And this shows right after this info shows up

  Processed 1768 pages for database '[database]', file '[logic_name]'
  on file 1. Processed 4 pages for database '[database]', file
  '[logic_name]_log' on file 1.

I checked the NETWORK SERVICE user and it has access to the folder of the .bak file. I noticed that under the general Security options (not the ones just for the database I want to restore, the outer ones in the tree of the Microsoft SQL Server Management Studio) the NT AUTHORITY\NETWORK SERVICE user was missing, so I created it with the same options as the one I have on my computer right now, but this didn't change anything.

I set the LockTimeOut to 0 (it was -1), but again, nothing changed.

What can cause this timeout expiration? And how can I have different behaviour on different computers? I guess there is some option that I'm missing, but I really don't know what else to do.
Thanks!

P.S.: this is the query I use to back up the database

BEGIN
SET NOCOUNT ON;
BACKUP DATABASE [{0}] TO  DISK = N'{1}'
WITH NAME = N'program-Full Database Backup', STATS = 10;
END

You have no problem with permissions (otherwise you'd got "access denied" or "file not found" errors), nither it's a lock timeout. This timeout is set up in your C++ program, if I'm not mistaken, 30 seconds is the default. SQL Server has no timeouts at all, you should change the property in your application, smth like SqlCommand.CommandTimeout


Database disaster prevention My database is larger than 250GB. I make scheduled backups with a third party Tool.

Are schedule database backups the best way to protect SQL Server database from corruption?   Or can you recommend something else?

A backup can't prevent corruption. Nothing can absolutely prevent corruption.

What a backup does is to enable you to recover data to an earlier point in time in the event of corruption or other disaster.

Whether your backup schedule protects you adequately is not something a community of peers can answer - your business requirements will dictate that. You need to consider your recovery point objective (RPO) - how much data can you afford to lose? If you cap at 5 minutes and you take a full backup once a day and log backups every hour, you're not covered. To truly protect yourself you should be in full recovery mode and your log backups should occur every <= n minutes, where n is the maximum amount of data loss you can sustain.

There are other considerations as well, such as recovery time objective (RTO) - in the event of a disaster, how long can you afford to spend getting back online? This will determine whether you just keep your backups on a drive ready for access, or actively keep a warm standby using log shipping, or higher "cost" DR techniques such as mirroring, FCIs, and Availability Groups. Note that some of these technologies can help save you from data corruption but other types of failures aren't covered (e.g. an accidental delete without a where clause will happily run on both the primary and any secondaries).


How to get lsn(xid) numbers of transactions from WAL file? I'm looking for a way of getting transactions from WAL files.
Basically I need their LSN numbers.
(ideally both transactions LSN numbers and WAL files first and last LSN numbers)
I have a PostgreSQL cluster binary backup and set of WAL files.
I would like to perform PITR but first I need to find the point where I need to restore to.
So I need to find a particular transaction LSN or XID number which later I'm goin to use as recovery_target.
Could you please give an idea how to get LSN numbers or XID numbers from WAL file ?

Run pg_waldump on the WAL segment.
Then you will get lines like
rmgr: Heap        len (rec/tot):    115/   115, tx:        564, lsn: 0/01FECDB0, prev 0/01FECC88, desc: HOT_UPDATE off 1 xmax 564 flags 0x60 ; new off 2 xmax 0, blkref #0: rel 1663/16385/1417 blk 0

In there you have tx: 564, lsn: 0/01FECDB0, which is your desired information.


How to do a SELECT inside of a PIVOT I am trying to SELECT values from a column in my database in order to perform a PIVOT on those values without hard-coding them.

SELECT *
FROM (SELECT ItemType, [Status] FROM [EVAULTTEST].[dbo].EvidenceItems WHERE ItemType IS NOT NULL) AS ei

PIVOT(
COUNT([Status])
FOR [Status]
    IN (SELECT DISTINCT ei.[Status] FROM [EVAULTTEST].[dbo].EvidenceItems AS ei)
) AS piv

However, the SELECT inside of our IN is giving us the error:

Incorrect syntax near 'SELECT'. Expecting '.', ID, or QUOTED_ID.

How can we use a SELECT inside of a FOR IN statement?

This is pretty much what bluefeet gave you as an example in the comments.

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Status])
                    from [EVAULTTEST].[dbo].EvidenceItems
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT [ItemType],' + @cols + ' from
             (SELECT ItemType, [Status]
                from [EVAULTTEST].[dbo].EvidenceItems) x
            pivot
            (COUNT([Status])
                for [Status] in (' + @cols + ')
            ) p '

execute(@query)


You would need to use a reduction method to slowly calculate the parts that make up the target volume. For instance 1.75, you would as you say be 1.00, then 0.5 then 0.25. You can use a loop to calculate the remainder using modulus, then use that remainder to find the next largest volume less than or equal to your value.

This is how I would do it (there might be better ways but this works)

CREATE TABLE VolumePrice (
    id int not null,
    volume decimal(9,4) not null,
    price decimal(9,2) not null
);

insert VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),
       (2,0.2500,14251.10),
       (3,0.2500,14250.00),
       (4,0.0050,14200.00),
       (5,0.5000,14255.01);

-- create a temp table to hold the output
create table output (
    id int auto_increment not null primary key,
    remainder decimal(9,2),
    volume decimal(9,2)
);

DELIMITER //

create procedure do_insert()
begin
set @v = 1.75;
set @remainder = @v;

while @remainder > 0
do
    insert output(remainder, volume)
    select @remainder % max(volume)
         , max(volume) as volume
      from volumeprice
     where volume <= @remainder;

  set @remainder = (select remainder
                      from output
                  order by id desc
                     limit 1);
end while;
end;
//

DELIMITER ;

CALL do_insert();

select v.*
  from output as o
  join (select volume, max(price) as max_price
          from volumeprice
      group by volume) as vp on vp.volume = o.volume
  -- I only link back to the volumeprice table here to obtain the id,
  -- if you do't need the id you can remove this part and just select vp.* instead...
  join volumeprice as v
    on v.volume = vp.volume
   and v.price = vp.max_price;

And here is a SQL Fiddle that seems to do the trick.


REPMGR - change reconnect_attempts and reconnect_interval not working I configured PostgreSQL automatic failover with REPMGR. But, in my simulation the sleeping time is 1 second for each reconnect attempt:

If I consult official documentation the default value to reconnect interval is 10 seconds.

#reconnect_interval=10            # Interval between attempts to reconnect to an unreachable
# primary (or other upstream node)

I tried to change values of reconnect attempts and interval in repmgr.conf and restart service of repmgr and use "repmgr standby register --force" but doesn't work.
My REPMGR.CONF
node_id = 2
node_name = 'PGCluster02'
data_directory='/var/lib/postgresql/12/main'
conninfo = 'host=PGCluster02 port=1010 user=my_repmgr dbname=dbrepmgr connection_timeout=2'
use_replication_slots=1
reconnect_attempts=30
reconnect_interval=10
failover=automatic
promote_command='/usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

Environment
PostgreSQL Version: 12.5
REPMGR Version: 5.2
Debian version: 10

Other tools in my environment are Barman (another VM) and PGBouncer (current VM)
How can I change the reconnect interval to 10 or 15 seconds? Anyone can help me?
I want to put my failover time to 5 minutes.
Thank you!!

I had the same problem, it's already solved:
https://github.com/2ndQuadrant/repmgr/issues/673

Issue has been reproduced and a fix applied, which will be in the upcoming 5.2.1 release.


How to check when statistics was last executed? We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have been recently updated via SQL Management Studio?

I apologize if this question isn't explaining this very well - I've only been introduced to statistics until now and prior to this would look to indexes whenever I've had performance related issues.

Edit:

I'm using the following but receiving a syntax error:

use *databasename*
exec sp_autostats *schema.tablename*

The error I'm receiving is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

Why is this?

For your edit - You need to enclose table name into single quotes:

EXEC sp_autostats 'tablename'

and for the original question

First - find the statistics you want to check:

Second - see its properties, and there you will see the last updated timestamp:

Or you may want to execute the following query:

SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'


Sending an automated email through a nightly job I had the following SQL query:

select substring(PostOfficeBox,1,4) as 'Postcode'
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = 'BT'
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

I now am trying to set this up as a nightly job to run and email myself the results but have had problems when trying to convert this to an automated stored procedure:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'test@mail.co.uk',
@subject = 'Post Code Analysis',
@query = N'select substring(PostOfficeBox,1,4) as ''Postcode''
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as ''New Connections''
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as ''Domestic Metered''
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as ''Commercial Metered Low''
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as ''Commerical Metered High''
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as ''Domestic Keypad''
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as ''Generator''
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as ''Commercial Keypad''
from AccountExtensionBase as a
JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = ''BT''
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4);',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'pca-test.txt'

When I run this query it is saying 'AccountExtensionBase' is not a valid object but it is contained within the TestCRM database that my SSMS is set to. Any help would be greatly appreciated,

The query will be executed by sp_send_dbmail asynchronously, on a separate session that has msdb as current database. So you must use three-part names in your query:

@query = N'select substring(PostOfficeBox,1,4) as [Postcode]
...
from TestCRM.dbo.AccountExtensionBase as a
JOIN TestCRM.dbo.CustomerAddressBase as b ON a.AccountId = b.ParentId
...'


Navicat Premium doesn't show/list tables in postgres database schema I have a local instance of postgres in my Windows machine, when I explore the databases and schemas in PgAdmin4 I can see every table and make queries and etc.

But when I connect to this same postgres instance with same host, user and password with Navicat Premium it only show me the databases but I cannot see the tables in them, but if I do a query I get the result.

Ran into this same problem today after upgrading to Postgres 12. An update of Navicat to the current latest version (12.1.27) solved the issue for me.


Oracle database installation directory using SQL I have server that has had Oracle installed by someone else. I am able to connect to it using SQL developer and do all the operations, but I want to know the location where Oracle is installed.

I need this details to execute exp, imp, expdp, impdp, etc. commands using putty to perform import/export and data dumps.

Is there any way I can get the Oracle installation directory details using SQL command? Does Oracle store the installation path in a database?

I am sure there is a even smarter way, but if you use the default trace file directories, which is usually the case, you will in most cases find both ORACLE_BASE and ORACLE_HOME via

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

This gives you the absolute path to the trace files and they are inside ORACLE_BASE and ORACLE_HOME respectively.

HTH KR Peter

Edit: Have a look here for a more straight forward approach: https://dba.stackexchange.com/questions/97390/query-to-get-oracle-home-path-in-oracle-11g


Is there a way to allow a proxy connection to use passed authentication in a public database link? I think the answer to this is no, but I could be wrong.  If nothing else it might be nice for others to be aware of this limitation of proxy connections.

No, in Oracle 11.2.0.2 proxy connections cannot use passed authentication in a public database link.  Switch to a private database link with specified authentication.


Reduce load off primary server by using log shipping, or mirroring, or something else? I have previously asked the below question about LogShipping frequencies, but I think I might be approaching this problem incorrectly (LogShipping frequency)

Problem

I have a high volume site that I would like to divert some of the load off the primary server. The main area I wish to divert is the admin reporting queries which do not require the data to be real time (a lag of 5-10 mins is ok).

Experiments

LogShipping

This appeared to be ok, but I have an issue with the two ways that it handles connected clients.

Disconnect on restore. If I have a long running report from the web app, it will kill the report in the middle of processing.
Dont disconnect user. This is also not very good because if the admin db is getting a high amount of traffic it cannot get an exclusive lock on the db to do the restore and the lag of the data difference could push out to be more than 10+ mins.

Mirroring

This looked very promising, but seems more focused around data redundancy and the ability to have a failover db, not my actual aim of reducing overhead on the primary server.

Question

Is the something I am missing here, should I be trying something else (readonly database, better hardware, clustering), or am I completly missing the point of the above technologies?

Now that SQL Server 2012 has shipped, don't forget about AlwaysOn, which allows explicitly for reads scale out.

Mirroring works for what you describe, but the orchestration of database snapshots for reporting can get hairy (the mirror database is unreadable, you must report from database snapshots created periodically).

I'm not a fan of transactional replication, but it can be used for your scenarios and others more knowledgeable than me have came out In Defense of Transactional Replication.

Both of AlwaysOn and Transactional Replication offer what you want (scale out reads w/o disruption) and they both achieve near-real-time data latency. They differ though on price point: AlwaysOn is an SQL Server enterprise only feature and requires Windows Clustering which in turn requires Windows enterprise licensing, ie. it costs. Plus it requires an upgrade to SQL Server 2012. Transactional Replication on the other hand costs basically nothing in addition to what you already have (except if your whole setup is on Express Edition instances, which I doubt) but it comes at a cost because it will create friction with your development/deployment cycle, which now has to consider the replication impact (must add new articles to the publications, certain schema change operations require careful planning etc).

And for some out-of-the-box thinking consider how some have resorted to Using Service Broker instead of Replication.


MyRocks vs Cassandra data size Both MyRocks (MySql) and Cassandra uses LSM architecture to store their data. So I have populated around 5 million rows in MySql with MyRocks as storage engine and also in Cassandra.
In Cassandra it takes only 1.7 GB of disk space while in MySql with MyRocks as storage engine, takes 19 GB.

Am I missing something? Both uses same LSM mechanism. But why they differ in data size? Can someone please explain?

And an update to the above question. I guess it has something to do with the text column.

Tried with another Table Structure : (bigint,bigint,varchar,text).
No Of rows Populated : 300 000
Now in MyRocks the data size is 185MB
In Cassandra - 13 MB.

But if I remove the text column then,

MyRocks - 21.6 MB
Cassandra - 11 MB

Any idea about this behaviour?

Well the reason for the above behaviour is due to the rocksdb_block_size set to 4kb. Due to smaller data blacks the compressor finds lesser amount of data to compress. Setting it to 16kb solved the issue. Now I get the similar data size as of cassandra.


Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and results were returned.

My question is how do I modify the statement so that I can still filter on those conditions?

Stored Proc code:

SELECT
    REPORT_SPOOL.ID,
    REPORT_SPOOL.REPORT_SPOOL_TYPE_ID,
    REPORT_SPOOL.FUND_ID,
    REPORT_SPOOL.PERF_ENTITY_ID,
    REPORT_SPOOL.REPORT_GUID,
    REPORT_SPOOL.REPORT_TEMPLATE_GUID,
    PERF_ENTITY.CODE AS PERF_ENTITY_CODE,
    PERF_ENTITY.NAME AS PERF_ENTITY_NAME,
    FUND.CODE AS FUND_CODE,
    FUND.NAME AS FUND_NAME,
    REPORT.CODE AS REPORT_CODE,
    REPORT.NAME AS REPORT_NAME,
    REPORT_TEMPLATE.CODE AS REPORT_TEMPLATE_CODE,
    REPORT_TEMPLATE.NAME AS REPORT_TEMPLATE_NAME,
    FUND.ACCOUNTING_START AS START_DATE,
    FUND.ACCOUNTING_END AS END_DATE
FROM
    PERF_ENTITY
    RIGHT OUTER JOIN
        REPORT
        INNER JOIN
            REPORT_SPOOL
            ON REPORT.GUID = REPORT_SPOOL.REPORT_GUID
        INNER JOIN
            REPORT_TEMPLATE
            ON REPORT_SPOOL.REPORT_TEMPLATE_GUID = REPORT_TEMPLATE.GUID
    ON PERF_ENTITY.ID = REPORT_SPOOL.PERF_ENTITY_ID
    LEFT OUTER JOIN
    FUND
    ON REPORT_SPOOL.FUND_ID = FUND.ID
    WHERE
    (END_DATE IS NULL OR END_DATE > @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] >= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] <= @REPORT_DATE)

Those last 2 SELECTS are the issue

I found the solution.

I replaced the = (equal signs) to IN.

The WHERE clause now looks like this:

 WHERE
    (END_DATE IS NULL OR END_DATE > @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] >= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] <= @REPORT_DATE)


How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed I just switched my Wordpress databases to use the InnoDB engine.

When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work.

I manually ran OPTIMIZE TABLE and the suggested ALTER TABLE but none of them removed the overhead. I ran mysqltuner on my server which showed 14 fragmented tables - I have a total of 14 tables.  Even after running optimize and alter tables, all 14 tables are still fragmented!

How can I properly OPTIMIZE and REPAIR InnoDB tables?

Ouch you have innodb_file_per_table off

I wrote articles on this before. You will have to convert the InnoDB infrastructure.

Oct 29, 2010 : My Original Post in StackOverflow
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

Once you do this, anytime you do OPTIMIZE TABLE to an InnoDB, it will actually shrink.

If you do not perform the InnoDB infrastructure cleanup, the ibdata1 file will actually grow in size. To make matter worse, I think mysqltuner.pl is using a weird algorithm for determining InnoDB fragmentation. I tend to ignore it.

I check for fragmentation differently.

Back on April 19, 2012 I answered a question similar to this: How do you remove fragmentation from InnoDB tables?.

Here is what I said in that earlier post:

  After running the above steps, how can you determine what tables need
  to be defragmentated? It is possible to find out, but you will have
  script it.

  Here is an example: Suppose you have the table mydb.mytable. With
  innodb_file_per_table enabled, you have the file
  /var/lib/mysql/mydb/mytable.ibd

  You will have to retrieve two numbers

  FILESIZE FROM OS : You can ascertain the filesize from the OS like
  this

ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'

  FILESIZE FROM INFORMATION_SCHEMA : You can ascertain the filesize from
  information_schema.tables like this:

SELECT (data_length+index_length) tblsize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

  Just subtract INFORMATION_SCHEMA value from OS value and divide the
  difference by the INFORMATION_SCHEMA value.

From there you would decide what percentage deems it necessary to defrag that table. In other words, if there is a big difference between the filesize of the .ibd file and the number from INFORMATION_SCHEMA.TABLES, go ahead with OPTIMIZE TABLE. Regardless of the filesize, please don't worry if the fragmentation is less than 1%.


Foreign keys - link using surrogate or natural key? Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack Douglas' answer in this question, and his reasoning seems sound to me. I'm aware of the discussion beyond that that rules change, but this would be something that would need to be considered in any situation.

The main reason for asking is that I have a legacy application that makes uses of FKs with natural keys, but there is a strong push from devlopers to move to an OR/M (NHibernate in our case), and a fork has already produced some breaking changes, so I'm looking to either push them back on track using the natural key, or move the legacy app to use surrogate keys for the FK. My gut says to restore the original FK, but I'm honestly not sure if this is really the right path to follow.

The majority of our tables already have both a surrogate and natural key already defined (though unique constraint and PK) so having to add extra columns is a non-issue for us in this insance. We're using SQL Server 2008, but I'd hope this is generic enough for any DB.

Neither SQL nor the relational model are disturbed by foreign keys that reference a natural key. In fact, referencing natural keys often dramatically improves performance. You'd be surprised how often the information you need is completely contained in a natural key; referencing that key trades a join for a wider table (and consequently reduces the number of rows you can store in one page).

By definition, the information you need is always completely contained in the natural key of every "lookup" table. (The term lookup table is informal. In the relational model, all tables are just tables. A table of US postal codes might have rows that look like this: {AK, Alaska}, {AL, Alabama}, {AZ, Arizona}, etc. Most people would call that a lookup table.)

On big systems, it's not unusual to find tables that have more than one candidate key. It's also not unusual for tables that serve one part of the enterprise to reference one candidate key, and tables that serve another part of the enterprise to reference a different candidate key.  This is one of the strengths of the relational model, and it's a part of the relational model that SQL supports pretty well.

You'll run into two problems when you reference natural keys in tables that also have a surrogate key.

First, you'll surprise people. Although I usually lobby strongly for the Principle of Least Surprise, this is one situation where I don't mind surprising people. When the problem is that developers are surprised by the logical use of foreign keys, the solution is education, not redesign.

Second, ORMs aren't generally designed around the relational model, and they sometimes embody assumptions that don't reflect best practice. (In fact, they often seem to be designed without ever having input from a database professional.) Requiring an ID number in every table is one of those assumptions.  Another one is assuming that the ORM application "owns" the database. (So it's free to create, drop, and rename tables and columns.)

I have worked on a database system that served data to hundreds of application programs written in at least two dozen languages over a period of 30 years.  That database belongs to the enterprise, not to an ORM.

A fork that introduces breaking changes should be a show-stopper.

I measured performance with both natural keys and surrogate keys at a company I used to work at. There's a tipping point at which surrogate keys begin to outperform natural keys. (Assuming no additional effort to keep natural key performance high, like partitioning, partial indexes, function-based indexes, extra tablespaces, using solid-state disks, etc.)  By my estimates for that company, they'll reach that tipping point in about 2045. In the meantime, they get better performance with natural keys.

Other relevant answers: In Database Schema Confusing


SSDT Publish Window: What does checkbox ENABLE mean? In the Visual Studio 2015 SSDT "Advanced Publish Settings" Window, Tab General, "Advanced Deployment Options" there are several checkboxes in the column "ENABLED" we can check or uncheck.

Most of the descriptions in there (that are also documented here) say something like this:

  "Specifies whether differences in the database collation should be
  ignored or updated when you publish to a database."

Now, it's completely undefined what exact consequences checking this box has: ignored OR updated? Does it ENABLE IGNORE or ENABLE UPDATE?

Does anyone know?

It is so annoying that people create software and never ever notice that their wording is misleading and unclear...

The checkbox applies to each option "positively" so that e.g. ticking "script database collation" will generate the appropriate statements to alter collation settings. If not ticked, the deployment will ignore those differences by not scripting the changes.


Sql Service broker BEGIN DIALOG CONVERSATION and SEND. Should they be in a transaction? I regularly see code that looks like this:

BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @u....
SEND ...
COMMIT;

BOL for BEGIN DIALOG doesn't use the transaction handling in the examples and it doesn't mention transactions at all.

Are transactions needed?  If so why?

You are correct that the BEGIN DIALOG CONVERSATION does not specify using a TRANSACTION for starting a Service Broker dialog. But many developers use them as a standard practice.

For example, the 2008 R2 TechNet page Beginning a Conversation and Transmitting Messages seems to recommend BEGIN TRANSACTION and COMMIT TRANSACTION (or ROLLBACK) as a good practice.

This topic Configuring Service Broker for Asynchronous Processing also shows transactions being used.

These multi-step examples that use transactions do so for the same reason transactions are used in other code.  Namely to ensure either a complete transaction or else a rollback to a consistent state.

Of course, you can decide if your Service Broker dialog is simple enough that you do not need a transaction. But that is up to you to decide.


map user in oracle 10g here is the scenario:

There are two schema created in oracle 10g, say db 1 and db 2. db1 can be accessed by username : sys with password and db2 can be accessed by username : test with password.

How is it possible to access db1 using username : test with password?
In MSSQL there is an option for user mapping to different database. is there any such options in oracle 10g to do the same?

There is no similar option assuming that "db1" and "db2" are actually Oracle databases.

What SQL Server calls a "database" is roughly equivalent to what Oracle calls a "schema".  In Oracle, you would generally only run one database on a server though that database may have many different schemas to support multiple applications.  Each Oracle database has a completely separate set of users.

You could create a database link in db2 that connects to db1 and allow the test user to use that database link to access objects in schemas on the db1 database.  Going further, you could use that database link to replicate data to db2 from db1 so that you could query it locally.  That's as close as you'll get to exposing data in one database to a user in another database.

Of course, from your description, it is entirely plausible that you merely have two schemas in the same database in which case you can freely grant access on objects in any schema to any user using the appropriate GRANT statement.


MySQL hang during "show collation" on Amazon RDS MySQL is hanging when I try to do SHOW COLLATION on my Amazon RDS instance.  I can use SHOW COLLATION fine with my local database, and other SQL commands work fine on my RDS instance. But Amazon SHOW COLLATION hangs every time.  Maybe I have too many collations?  I'd be perfectly happy just setting it to utf8 or Latin1 collation.

I'm using mysql(5.6.12) on a micro instance.

Any ideas on what I can do? Or how can I get more information on what's happening?

Not sure if you found a solution to this but for us it seems to be related to the size of data leaving the VPC.

If you run a query with a small result set:

show collation like '%utf8_swedish%';

The result returns fine, but if you take away the like and get a large result set back it fails. We're still investigating the problem but not sure you found a solution.


Same schema, same data, same execution plan? I have a T-SQL stored procedure in SQL Server 2014 SP2 that takes 3x as long on the server as it does from my local environment.

I restored my local database to what was on the server, so schema and data are the same.

I'm a programmer, not really a DBA, so bear with me on this.

The execution plans seem the same until you look at the XML. On my local environment, a Stream Aggregate is used for one of the queries to make a distinct select, while on the server, it looks like a Sort is used with a hash.

It appears that the fact that the server has less processors than my (beast) local machine is contributing to the problem. Every query takes a little longer on the server. Could this be the reason that the sort/hash is chosen on the server instead of the Stream Aggregate?

Is there a way to force the Stream Aggregate?

Here is the SQL for the stored proc: http://pastebin.com/kkXM3Bsf

Here is the execution plan for my local environment: http://pastebin.com/0u7SHxnM

Here is the execution plan for the server: http://pastebin.com/9sE9DfM0

The hardware configuration can affect plan selection as I discuss in my answer to the related Q & A:

difference in execution plans on UAT and PROD server

The question doesn't quantify the "3x" performance difference, but assuming it is important enough to be concerned about:

You can force the broad shape of a plan completely using a Plan Guide or USE PLAN hint. This will prevent the optimizer choosing a different plan shape over time as the data volume and distribution changes, so it is important to review forced plans regularly.
You can often get closer to the desired plan shape with targeted FORCESEEK hints on tables that should be accessed by a seek (e.g. driven by a nested loops join) rather than a scan (with a hash or merge join). In your case, the FORCESEEK hint(s) could be applied to Account and/or CustomerLocation. This may be preferable to forcing the whole plan shape as in the previous point, as it gives the optimizer freedom in other areas of the plan.
You could give the optimizer a better chance with the @country is null or ca.Country = @country predicate by using a RECOMPILE hint, or writing two separate queries (one for the NULL case; the other for non-NULL) surrounded by an IF @country IS NULL test. Try the two separate queries first.
You should be able to avoid the sort spill with a QUERYTRACEON (7470) hint. This is documented in:

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
There is no supported way to force a Stream Aggregate (over a Sort Distinct), without forcing the whole plan.
You should consider indexing the heap table variable (a PK at least), and/or using a temporary table instead.

I would start by writing the query as two alternatives (or using the RECOMPILE hint, if you can tolerate a recompilation on each execution), with the 7470 trace flag enabled, and go from there. Plan forcing would be my least favourite option.

If the server has a very large amount of memory, and plans routinely prefer memory-consuming operators when they should not, you could consider testing your whole workload with TF 2335 as in the linked Q & A. I would try the more targeted approaches first.

See also my article: Parameter Sniffing, Embedding, and the RECOMPILE Options


Meaning of "nonclustered located on primary" When I run sp_help (using ALT+F1) for a table, on the indexes, there is a column index_description. and the rows have for example:

nonclustered located on PRIMARY (on foreign keys)
nonclustered, hypothetical located on PRIMARY (on non clustered indexes I created)
clustered, unique, primary key located on PRIMARY

I am more interested in the located on PRIMARY part, What does it mean?

This is the name of the filegroup or partition scheme that the index is created on. This can be specified when creating an index with a second ON clause.

The sp_help procedure calls sp_helpindex which retrieves the name from sys.data_spaces

  The primary filegroup contains the primary data file and any other
  files not specifically assigned to another filegroup. All pages for
  the system tables are allocated in the primary filegroup.

More info about Files and Filegroups here


Grouping Subsets of Rows with Null Values within an Ordered Set Lets say we have an table where each row is a day, and it is ordered by this day column. Then we have left joined a membership data set showing which day the members were active (and not).

Lets say our current data set looks like this... Membership was active from day 3-5, inactive from 5-8, and active from day 9 onward etc.

DAY     DATE        MEMBER  ACTIVE
 1      2017-01-01  123     null
 2      2017-01-02  123     null
 3      2017-01-03  123     2017-01-03
 4      2017-01-04  123     2017-01-04
 5      2017-01-05  123     2017-01-05
 6      2017-01-06  123     null
 7      2017-01-07  123     null
 8      2017-01-08  123     null
 9      2017-01-09  123     2017-01-09
10      2017-01-10  123     2017-01-10

...so ACTIVE=null means membership was not active on those days.

With this data structure, I would like to get to a "collapsed" set, showing "spans" of time inactive/active:

MEMBER  MIN(DATE)   MAX(DATE)   STATUS
123,    2017-01-01, 2017-01-02  INACTIVE
123,    2017-01-03, 2017-01-05  ACTIVE
123,    2017-01-06, 2017-01-08  INACTIVE
123,    2017-01-09, 2017-01-10  ACTIVE

I have tried using row_number() to somehow partition out the subsets of a certain status, but in this case, using min()/max() over the rows where ACTIVE is null, treats those as a single group, when in reality, there are several distinct spans of "inactive membership".

How can I distinguish the spans of inactive membership from one-another for grouping purposes?
What technique can I use to achieve that output above?

Here is the script to generate the dummy source data:

CREATE TABLE ##SRC (ID INT, D DATE, MEMBER INT, ACTIVE DATE);

INSERT INTO ##SRC (ID, D, MEMBER, ACTIVE)
SELECT 1, '2017-01-01', 123, NULL UNION
SELECT 2, '2017-01-02', 123, NULL UNION
SELECT 3, '2017-01-03', 123, '2017-01-03' UNION
SELECT 4, '2017-01-04', 123, '2017-01-04' UNION
SELECT 5, '2017-01-05', 123, '2017-01-05' UNION
SELECT 6, '2017-01-06', 123, NULL UNION
SELECT 7, '2017-01-07', 123, NULL UNION
SELECT 8, '2017-01-08', 123, NULL UNION
SELECT 9, '2017-01-09', 123, '2017-01-09' UNION
SELECT 10, '2017-01-10',    123, '2017-01-10'
;

Your sample data does not match your description and confused me at first. As sp_BlitzErik points out this is an island-and-gap problem. The solution is pretty straightforward if you have access to window functions. First, we can enumerate the table per member alone, let's call this full_order (this happens to be the same as day, but I'll ad it for generality). Second, we can enumerate the table per member and whether they were active on that day, let's call this partial_order

 select day, active, date, member
      , row_number() over (partition by member
                           order by day) as fullorder
      , row_number() over (partition by member
                          ,case when active is null then 0 else 1 end
                          order by day) as partialorder
 from src

DAY         ACTIVE     MEMBER      FULLORDER            PARTIALORDER

----------- ---------- ----------- -------------------- --------------------
      1 -                  123                    1                    1
      2 -                  123                    2                    2
      3 01/03/2017         123                    3                    1
      4 01/04/2017         123                    4                    2
      5 01/05/2017         123                    5                    3
      6 -                  123                    6                    3
      7 -                  123                    7                    4
      8 -                  123                    8                    5
      9 01/09/2017         123                    9                    4
     10 01/10/2017         123                   10                    5

If the difference between full_order and partial_order changes, it means that active has changed from null to a value, or vice versa. Therefore we can form a group with this difference. Within each such group we can pick the min(active) and max(active) to form an interval:

select member, grp, min(date), max(active)
from (
    select day, active, date, member
         , row_number() over (partition by member order by day)
         - row_number() over (partition by member
                             ,case when active is null then 0 else 1 end
                              order by day) as grp
    from src
)
group by member, grp

MEMBER      GRP                  3          4
----------- -------------------- ---------- ----------
    123                    0 01/01/2017 -
    123                    2 01/03/2017 01/05/2017
    123                    3 01/05/2017 -
    123                    5 01/08/2017 01/10/2017

It's probably easiest to add another level of nesting to get the desired result:

select member, min_active
     , coalesce(max_active, min_active) as max_active
     , case when max_active is null then 'INACTIVE' else 'ACTIVE' end as status
from (
    select member, grp, min(date) as min_active, max(active) as max_active
    from (
        select day, active, date, member
             , row_number() over (partition by member order by day)
             - row_number() over (partition by member
                                 ,case when active is null then 0 else 1 end
                                 order by day) as grp
        from src
    )
    group by member, grp)

MEMBER      MIN_ACTIVE MAX_ACTIVE STATUS
----------- ---------- ---------- --------
    123 01/01/2017 01/01/2017 INACTIVE
    123 01/03/2017 01/05/2017 ACTIVE
    123 01/05/2017 01/05/2017 INACTIVE
    123 01/08/2017 01/10/2017 ACTIVE


Ola Hallengren scripts: after a job failure, how to skip the databases that worked and backup only the remaining ones? In our organization we have more than 150 databases in one instance and full backup should run for every day.
Our backup script should be in such a way that if backup job fails while taking 100th database and if I rerun the job again, it should start from the failed database(100th) and exclude the backups which are already taken and the script should take the backup of the remaining databases which are not backuped up for the day. I think the current backup script won't serve this purpose.

Usually when one or more backups fails, you have to do some kind of manual check.
Check the log, fix the problem and the rerun the backup.
Focusing on the last step, I suggest you to collect the database list of failed backups from the commandlog table populated by ola scripts. Then you should use it as @databases parameter for the ola script.
In an daily backup jobs, you can use the ALL_DATABASES standard ola parameter as step 1 and the job ends there on success.
You can have a step2 as recovery step with the dynamically generated database list. You run it only to recover missing backups.