Where are my .NDF files located? I have created a sample database called TestDB. After that, I ran this select:

select * from sys.master_files

I found my .MDF, .LDF files from this select. Then I want to find out the .NDF file location . Is it possible to see those files like .NDF, .MDF , .LDF?

NDF is secondary data file, By default it will not be created unless we create the file while creating SQL server database or manually create it after database is created using AlTER DATABASE statement. SQL server by default will create one primary data file and one log file. If it is not visible in sys.master_files means you have not created it.

Read more about database files and filegroups

http://msdn.microsoft.com/en-us/library/ms189563.aspx

To add secondary data file please see

http://msdn.microsoft.com/en-us/library/ms189253.aspx


How to restore one table from full backup in Sybase ASE I need to retrieve 2 data records that were in a single table from yesterday's backup.

How can I restore a single table from a full backup in Sybase?
Or is it possible to retrieve the required records from the dump file itself?
Can I somehow load the dump file without recovering to the production database?

I don't have enough disk space to recover the database to a temporary database on the same disks/server.

Take a look at the ASE Archive Database feature.

This features allows you to mount your dump file(s) as a read-only database; once mounted you can access the tables in the database with your desired select queries.

Technically ...

you'll need to setup a smallish scratch database for managing the mount
it's possible to make non-persistent changes to data; this consists of allocating (optional) disk space to hold any modified pages (these changes will not be reflected in the database dump file(s)

For an example of setting up the scratch and archive databases, see the Reference manual entry for the create archive database command.


Why do I have multiple (unassociated) temporal history tables? I have been setting up a proof of concept system which has a SQL Server 2017 back end.
The system uses temporal tables to record asset configurations and track changes over time.
I have a Data table which is linked to the history table, let's call it dbo.MSSQL_TemporaryHistoryFor_12345678900.

So far so good. I have two issues:

Today I turned off versioning on the table so I could add a computed column. This was done and turned back on again without errors.

Now I find that I cannot query any historic data from before the change. New data is being added to the history, but there is nothing beforehand.

Looking inside SSMS, I can now see there are multiple history tables, all with the same name but with a hex suffix, e.g. dbo.MSSQL_TemporaryHistoryFor_12345678900_A0B1C2D3. They are not linked beneath the main data table. They are just floating around on their own inside the database. When I queried sys.tables, these are not shown as history tables and are not linked to the main data table.

These tables do contain the historical data which is missing.

The questions I have are therefore:

What do these additional tables represent?
How were they created?
Is there any way to somehow relink these into the main history chain so I can get my historical reporting back?

It's very frustrating so any help you can provide would be gratefully received. Thanks.

You need to provide the name of the history table in order to maintain data continuity when turning system versioning off and on.  This behavior is mentioned in the documentation for ALTER TABLE:

  If you don't use the HISTORY_TABLE argument, the system generates a new history table matching the schema of the current table, creates a link between the two tables, and enables the system to record the history of each record in the current table in the history table.

Here's a demo.  I'll create the example table from the documentation:

CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON);

This results in a history table named MSSQL_TemporalHistoryFor_1253579504.  Now I'll disable and enable system versioning:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON);

And I'm in your exact situation:

Now I'll clean everything up:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Employee;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504_D0055BB4;

Then create the table with a specific history table name:

 CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Then turn system versioning off and on, but continue specifying the history table name:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Note: in your specific situation, you should be able to use this syntax to "reattach" one lost history table to your base table

No extra tables:

The takeaway

Always specify a history table name explicitly when creating temporal tables or enabling system versioning.

The MS docs now call this out specifically on the Stopping System-Versioning on a System-Versioned Temporal Table page:

  When turning system versioning back on, do not forget to specify the HISTORY_TABLE argument. Failing to do so will result in a new history table being created and associated with the current table. The original history table will still exist as a normal table, but won't be associated with the current table.


Can an index refer to only one or a subset of possible values? I am using Postgres.  The table looks something like this:

tran_id SERIAL PRIMARY KEY,
account_id integer NOT NULL CONSTRAINT fk_account_id REFERENCES base.account(account_id) ON DELETE CASCADE,
tran_type base.my_enum not null,
tran_note varchar(64) not null,
process_id integer not null,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP

I am interested in being able to quickly find only the rows where process_id is 0.  That value means that the row is pending for some kind of processing, non-0 rows are basically old log that I don't want archived because the application also needs to find the number of transactions for a certain account.  So it is convenient to have the processed and pending data in the same table.  I am not, however, interested in being able to quickly find rows where that field is anything other than 0 and I don't want to use resources on indexing those other values.

This table, in one implementation, has close to 200K accounts, each account around 200 transactions on average so about 40M rows, give or take.  At any given time, there are maybe a few dozen new transactions to process, i.e. WHERE process_id = 0.  Once it is processed, the process_id is updated to something other than 0.

I will never be searching for process_id that is other than 0.  So I would be interested in having an index that points to only rows where that value is 0 and not necessarily bother with other values so that the index is more compact and faster.  Is this possible?

You can create a partial index that includes all columns of the table:

CREATE UNIQUE INDEX not_processed
    ON transactions
        (tran_id, tran_type, tran_note, created_at)
    WHERE process_id = 0 ;


Postgres soft delete partition, what to do with Foreign keys? I have a table A that has a couple of other tables (B, C) attached to it (many rows in B and C for one row in A).

I'm having performance problems with A, so I thought I would partition it using a date_deleted column (NULL/NOT NULL) into 2 tables A_active and A_deleted.

Data flow is as follows:

Records are created at a rate 10k per day, inserted into A_active
After 2 months records are moved to A_deleted
Records are never un-deleted
Nothing is ever hard deleted, currently there are 1.3M rows

I have written a full script to split my A into A_active and A_deleted, create triggers for inserts and updates, but I'm at a loss about the foreign keys.

Should I create B_active and B_deleted? The triggers will get super complex.

Should I create 2 FKs from B? One to A_active, one to A_deleted? Triggers will also be very complex.

Should I scrape this idea altogether? I kind of like the idea of having a dedicated table for historic records. It will simplify my selects.

A table of 1.3M rows is pretty small.  It would be unusual for a table of that size to benefit from partitioning.

Have you looked at the queries which are slow with EXPLAIN (ANALYZE, BUFFERS) to see what the problems are?

When partitioning works well, it is because it works hand-in-glove with your use cases.  So there can't really be generic partitioning advice.  You would have to tell us much more about the types of queries you run and what your performance expectations of them are, and what the current execution plans look like.


Postgresql: How to get row type of a table as text? I want to do this:
json_populate_record(null::schematable, json)

But schematable is a text variable contaning schema.table
I have tried this:
json_populate_record(pg_typeof(schematable), json)

but pg_typeof() returns regtype not rowtype.
How do I get a rowtype of a dynamic text argument?

There is no way to do this, since a type name is always an identifier, and only constants (literals) can be parameters.
The only resort is to use dynamic SQL like
EXECUTE format('json_populate_record(null::%I, $1)', 'schematable') USING json;


MySQL Workbench asking for password I am trying to set MySQL server on my Windows 7 64 bit machine. I have downloaded MySQL server and MySQLWorkbench.

I have started mysql service by command

C:\> net start mysql

as suggested here and checked that mysqld.exe process is running. Now when I do New Server Instance in Workbench, it is asking for the password. I never entered or set the password

My Question

Why New Server Instance in Workbench is asking for the password?

You will need to give root@localhost a password

Since you just installed mysql, you are running with default settings, this means you have no my.cnf. You will have to create one.

Run the following at a DIS Prompt

cd C:\Program Files\MySQL\MySQL Server 5.5
dir *.ini

There, you will see sample .ini files. Let's pick my-small.ini

net stop mysql
cd C:\Program Files\MySQL\MySQL Server 5.5
copy my-small.ini my.ini
cd data
del ib*
notepad my.ini

Once you open notepad, add this under the [mysqld] header

[mysqld]
skip-grant-tables

Close notepad and Save my.ini

Next, run these lines

net start mysql
mysql (hit enter)

You will get a mysql prompt. Now run this:

mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('mysecretpassword') WHERE user='root' AND host='localhost';
exit

Back at the DOS Prompt, do this:

net stop mysql
notepad my.ini

Once Notepad is open, delete the line skip-grant-tables from my.ini

Close notepad and Save my.ini

net start mysql

Once mysql is back up, test the password at the DOS prompt

mysql -uroot -p (hit enter)
Password: (type mysecretpassword and hit enter)

If you get the mysql prompt, CONGRATULATIONS !!! You installed a password for root@localhost

Go back to MySQL Workbench and user mysecretpassword as the password

Give it a Try !!!


Why does mysql treat "<" like it was "<="? I've tried the following command:

SELECT * FROM `mi_schedule` WHERE `day`<=(DATE_ADD(NOW(),INTERVAL 7 DAY)) AND `day`>=NOW()

Got 8 rows. Then I changed the <= to <, and nothing changed, still 8 rows.

So I changed the interval from 7 to 6:

SELECT * FROM `mi_schedule` WHERE `day`<(DATE_ADD(NOW(),INTERVAL 6 DAY)) AND `day`>=NOW()

and I got one less row from the results (7 total), as expected.

I switched back from < to <=, and again nothing changed.

I.e. apparently the < is considered to be a <= instead.

Is that intentional? According to the docs, it shouldn't.

PS: day is a DATE type.

(using version 5.5.29, engine InnoDB)

Are you working with datetimes?

Notice that If I ran it now I'd get for instance:

August, 28 2014 13:57:36+0000

Which means that if my dates are at midnight I'd get the same with < and <=

You should probably cast this into DATE if you are to work with dates


Using third party tools to backup Is there any advantages to use Third Party Tools to make Backups ?

I'm not referring one in particular but I want to know if someone using them to DB greater than 200 GB. Servers are mostly MSSQL 2008R2, Standard and Enterprise Editions.

This is just my input, but I have supported most of the major players in the 3rd party backup vendors. I have supported clients that utilize Idera, RedGate, Dell LiteSpeed, NetBackup (which does not really have compression but is 3rd party), and a few off-the-wall vendors that I can't remember anymore.

The improved compression you may or may not get with 3rd party products have a trade-off....CPU workload. I have seen Dell's (LiteSpeed) product run in SQL Server 2014 environment against a database that is right at 500GB, and using high compression kills the CPU. The result was only a backup file of around 64GB. It ended up causing SQL Server to stop providing resources to the backups since Dell's product uses the VDI API. The compression level had to be dropped down from 7 to about 3, this brought the backup size down to about 82GB. We took a native backup with compression enabled of same database, and it was right at 84GB in size. So is the trade off and cost worth it? test, test, test

I seem to recall a client I had a few years ago was using Idera's product and I had constant issues with the backups for their medical databases that were 1TB+ in size. I think some of it had to do with the disk subsystem the backups were being written to, which again is why you need to test. Overall Idera's GUI interface is not inviting to me, and could use great improvement. I would chose RedGate or Dell over Idera any day of the week.

You also have to remember that most 3rd party backup products are going to use the VDI API to SQL Server. This in and of itself can cause problems as well, frequent to often "BackupVirtualDevice" or "BackupIORequest" errors showing up in the error log. I have some of these be false positives (backup actually did occur) and some where legit failures. Make sure to read through the vendor support article or forums. RedGate and Idera have very good support systems in place and are very good about getting back with help. Dell I have not had to make any support calls with their product so far.

The one last thing I will bring up is the format of the backup taken by the 3rd party product. If you get into a DR situation are you going to have a standby server licensed with the product as well? I believe Dell allows you to install LiteSpeed without a license to do a restore, but it has to be licensed to do any backups. I think most of the vendors also include a utility to convert a vendor specific database to a SQL Server native backup file, in case of emergency.


Accessing file share from ETL package in SQL Always-On configuration How can I access the file share from ETL?

Here are my environment details:

I have 2 nodes in a windows cluster. node 01 and node 02
SQL Server 2014 is installed on both the nodes.
Both are Windows Server 2012 R2 machines
I have file share created in Azure portal and mapped as G: in both the nodes (01 and 02)

My ETL package is using C# code. It wants to read and write files on one shared folder for processing. I want it to use shared drive G.

Problem Statement: One of the nodes can be down at any time, so I cannot hard code as below:

//node01/G

Can I use file share feature (role) in a failover cluster? if yes, where will that new drive be stored?

I saw the post Accessing an Azure Storage Account File Share from an ASP.NET Application on Stack Overflow, but that requires code changes in all the ETL packages. Is that is the only way?

It sounds to me like you are suggesting that you require a highly available file share (see https://blogs.technet.microsoft.com/clausjor/2012/06/07/smb-transparent-failover-making-file-shares-continuously-available/). In order to do this you will obviously need Clustered storage (an ip and network name). It would be a little unusual to do this using your Availability Group's Windows Cluster since really that is only in place to provide Quorum and meta-data synchronisation/ mediation of AG Cluster HA objects -and I would personally advice against complicating that deployment -Clusters should be kept as simple and easy to troubleshoot as possible. Instead I would advise to use a remote fileshare - which you can either make highly available via another windows cluster or just use a standalone server (depending upon requirements).


Find and print the restaurant name and city for all restaurants serving Salad I'm new to SQL and having some trouble with this one, but I feel like the answer is fairly obvious and I'm missing something. Here is the query I've tried:

SELECT r.restaurantName, r.city
FROM Restaurant r, MenuItem m
WHERE m.itemNo = 44;

These are the tables I'm using:

CREATE TABLE Restaurant(restaurantID Integer Primary Key, restaurantName Char(15) NOT NULL, type Char(15) NOT NULL, city char(15) NOT NULL);

CREATE TABLE Dish(dishNo Integer Primary Key, dishName Char(15) NOT NULL, type ENUM ('ap', 'en', 'ds'));

CREATE TABLE MenuItem(itemNo Integer Primary Key, restaurantNo int, dishNo int, price DECIMAL(4,2) CHECK(price IN(price>=5 and price<=50)), FOREIGN KEY(restaurantNo) REFERENCES Restaurant(restaurantID) ON DELETE NO ACTION, FOREIGN KEY(dishNo) REFERENCES Dish(dishNo) ON DELETE SET NULL);

CREATE TABLE FoodOrder(orderNo Integer Primary Key, itemNo int, dateof date CHECK(dateof IN(dateof>='2017-01-01')), timeof time, FOREIGN KEY(itemNo) REFERENCES MenuItem(itemNo) ON DELETE CASCADE);

Inserted data:

INSERT INTO Dish Values(13, 'Spring Rolls', 'ap');
INSERT INTO Dish Values(15, 'Pad Thai', 'en');
INSERT INTO Dish Values(16, 'Pot Stickers', 'ap');
INSERT INTO Dish Values(22, 'Masaman Curry', 'en');
INSERT INTO Dish Values(10, 'Custard', 'ds');
INSERT INTO Dish Values(12, 'Garlic Bread', 'ap');
INSERT INTO Dish Values(44, 'Salad', 'ap');
INSERT INTO Dish Values(07, 'Cheese Pizza', 'en');
INSERT INTO Dish Values(19, 'Pepperoni Pizza', 'en');
INSERT INTO Dish Values(77, 'Veggie Supreme Pizza', 'en');

INSERT INTO MenuItem Values(0, 0, 13, 8.00);
INSERT INTO MenuItem Values(1, 0, 16, 9.00);
INSERT INTO MenuItem Values(2, 0, 44, 10.00);
INSERT INTO MenuItem Values(3, 0, 15, 19.00);
INSERT INTO MenuItem Values(4, 0, 22, 19.00);
INSERT INTO MenuItem Values(5, 3, 44, 6.25);
INSERT INTO MenuItem Values(6, 3, 12, 5.50);
INSERT INTO MenuItem Values(7, 3, 07, 12.50);
INSERT INTO MenuItem Values(8, 3, 19, 13.50);
INSERT INTO MenuItem Values(9, 5, 13, 6.00);
INSERT INTO MenuItem Values(10, 5, 15, 15.00);
INSERT INTO MenuItem Values(11, 5, 22, 14.00);

Example of one of the joins I have tried:

SELECT DISTINCT Restaurant.restaurantName, Restaurant.city
FROM Restaurant
INNER JOIN MenuItem
ON MenuItem.restaurantNo = Restaurant.restaurantID AND MenuItem.itemNo = 44;

SELECT Restaurant.restaurantName,
       Restaurant.city
FROM   Restaurant,
       MenuItem,
       Dish
WHERE  Dish.dishName = 'Salad'
       AND Dish.dishNo = MenuItem.dishNo
       AND MenuItem.restaurantNo = Restaurant.restaurantNo
ORDER  BY Restaurant.city,
          Restaurant.restaurantName


Can't enable MySQL 5.6 Query Cache I'm trying to enable MySQL Query Cache on Ubuntu 15.04 with MySQL 5.6.25

I've added this to end end of /etc/mysql/my.cnf and /etc/mysql/conf.d/mysql.cnf:

query_cache_type = 1
query_cache_size = 4096M
query_cache_limit = 2M
query_cache_strip_comments =1

The whole server has been restarted more than once.

user@myhost:/$ mysql
mysql: unknown variable 'query_cache_type=1'

Using SHOW VARIABLES LIKE '%query_cache%' confirms that query_cache_type = OFF

SET GLOBAL query_cache_type = 1;
/* SQL Error (1651): Query cache is disabled; restart the server with query_cache_type=1 to enable it */

How can I solve this?

You shouldn't put those lines at the very bottom of the file.

Look for the [mysqld] group header in my.cnf and put those lines under it

[mysqld]
query_cache_type = 1
query_cache_size = 4096M
query_cache_limit = 2M
query_cache_strip_comments =1

Those line cannot go under any other group header.

Then, restart mysqld.


What is the Maintenance DB asked by pgAdmin? pgAdmin dialog for adding new database connections asks for a "Maintenance DB".
In order to be able to connect, I set it to the database I want to connect (and also, I have the rights to connect).

So, why is it named "Maintenance DB" instead of "DB" or "Database"?.

It is worth mentioning that you are free to define any existing database in the cluster as "Maintenance DB". You are not limited to the options suggested by the interface, which are just typical defaults.

This is particularly useful for users with limited permissions or read-only access. Access to a single DB is simpler with the same DB as "Maintenance DB". Related:

Do all users need access to the postgres database in pg_hba.conf?

What is the maintenance db? The manual:

  The postgres database is also created when a database cluster is
  initialized. This database is meant as a default database for users
  and applications to connect to. It is simply a copy of template1 and
  can be dropped and recreated if necessary.


Can full drive backup be used instead of MSSQL database backup? I have a MSSQL Express server running on a VM.  Hosting provider I use has an option to create drive snapshots. The server is running all the time and is not stopped during the drive backup. Backup has no special functionality related to MSSQL. If I keep my data files on such drive and use snapshots in case of disaster recovery, are there any risks that database may not be restored?

Backup has no special functionality related to MSSQL.

Typically server backup programs will coordinate with SQL Server through the Windows Volume Shadow Copy Service (VSS) to ensure that SQL Server places the database files in a consistent state before volume snapshots are taken.  At a minimum backup programs should create backups that are consistent on a per-volume basis.

However, even in an enterprise environment where you have trusted professionals performing server backups using a configuration you can verify, very few DBAs would actually rely on server backups.  Remember backups alone are irrelevant.  Restores are what you actually care about.

So how are you going to perform a restore if you rely on server backups?  Not easily, that's for sure.

So take backups.  Then download them regularly from your hoster, or upload them to a cloud storage provider, eg:

Sign up for an Azure account
Provision a storage account (LRS + Cool default tier)
Use SQL Server's Backup to URL feature to take database backups directly to your storage account.


Can you put a SQL DB on a NAS? Can you put a SQL DB on a NAS drive? For example can I put the PostgreSQL DB "files" on a Sinology NAS or am I better to leave everything on a "real" PC? (like, say, a regular PC server running Linux)

I'm asking this question because something weird happened: some accounting app written in WinDev (a RAD tool kinda famous in some countries, like many french-speaking countries), using a HyperfileSQL DB stored on a NAS, just screwed up big times.

wikipedia HyperFileSQL (in french)

wikipedia WinDev (in french)

The entire DB got corrupted, many files had a size of zero and the developer of the application tell my clients: "It's your fault, you shouldn't have put the HyperfileSQL DB files on a NAS, NAS haven't got a CPU powerful enough, so corruption can happen".

I don't get this: HyperfileSQL is supposed to be ACID. I can understand delays and/or poor perfomances but complete corruption?

Heck, I should even be able to yank the "UPS cord" (I mean: the "power cord", but everything is on UPSes) from the NAS and a true SQL DB should be fine no!?

So can I put a SQL DB on a NAS?

Should I expect poor performance? (it's for tiny databases and I never noticed any perf issue)

Are complete DB corruption to be expected?

Databases require certain guarantees from the storage it runs on to implement ACID. Unfortunately, it is not always possible for the database to determine if those guarantees are met.

Here are some requirements that the storage (no matter if it is NAS, direct attached or SAN) must meet:

Write Ordering: Writes my happen in the order they are confirmed to the operating system. For example, if the database submits a write request for block A and B to the LUN, you can't have a situation where B is confirmed than A, but where the write happened in the opposite order.

Write through: When the storage says that something is written, it must be on permanent storage that will survive power failure. Unfortunately, a lot of operating systems and storage systems will cache the writes in DRAM unless you are careful. This means that if you lose connectivity to the NAS, you may lose data.

No Torn I/O When you submit a block of I/O and the I/O system confirms the receipt of that block, the entire block must be on stable media (for example, it is not allowed that a 8K page be split into 2 x 4K and the 8K write confirmed before both 4K blocks are written).

Atomic writes: Depending on the media, there is a minimum write size that is required to be atomic (typically 512B or 4K). Writes of this size either happen or they don't. There is no intermediate state that can be represented on the media. Note that this is not (quite) the same as torn pages protection. For example, with a torn page, you could be a situation where 4K of the 8K gets written but the 8K is not confirmed, you then crash and get corruption (which is detectable and typically fixable by the database after restart). With atomics, the write either happens or it does not.

Designing storage that is suitable for a database is no simple matter. Often, people get it wrong - especially if they did not test the storage before deploying. It is little comfort, but your situation is not that uncommon and NAS might have nothing at all to do with this (people get this wrong on direct attached storage and SAN too).

Databases can generally run fine on NAS if the storage is designed correctly and the NAS has the right guarantees.

Saying that the CPU of the NAS is not powerful enough is completely irrelevant to this discussion. So I think you can safely disregard the opinion of that developer.


How can I make this execution plan more efficient? I have worked out all the implicit conversions, but I still see mentions of it in the plan. I have attached the plan, and any recommendation will help.

select cardholder_index, sum(value) as [RxCost]
into #rxCosts
from RiskPredictionStatistics with (nolock) where model_name = 'prescription_cost_12_months'
 and model_set_name = 'rx_updated' and run_id in (select value from #runIds)
 and exists (select 1 from StringContainsHelper with (nolock) where IntValue = cardholder_index and ReferenceId = @stringContainsHelperRefId)
group by cardholder_index

https://www.brentozar.com/pastetheplan/?id=SyHl9xFeS

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX).

You could try converting the values there to get rid of the implicit conversion warnings.

Another possible improvement would be to alter the NonClustereIndex-Cardholder index on RiskProductionStatistics to have model_set_name as a key column, and model_name, run_id, value as included columns. This would address the Key Lookup.

You may also want to check the datatype of model_name. It appears in a Filter operator, and my fear is that it's a MAX datatype, which may prevent the predicate from being pushed down.

Since this is an estimated plan, and you haven't included any metrics about the query, it's hard to say how much improvement these changes will have.


User info retrieval takes too long - ASP.NET Membership I have an application that has been taken from a sharepoint user control and converted to plain web pages. Everything is working fine - I can sign up and retrieve my password, etc. However, when I try and display the registered users on a webpage the results will take 2+ minutes. Going to page 2 of results takes another 2+ minutes and so on. At last count we had less than 3000 users.

More details - In addition to rebranding the app to aspx pages, it has been connected to a different database server. The previous server (Server 2008, SQL server 2005) serves up membership pretty quickly, but the new server (Server 2016, SQL Server 2017) takes forever.  The new server is an Azure server (the original server was on prem).

We have other apps on the server that utilize the same Azure server. But they are nothing like ASP.NET Membership.

Is there a setting or configuration that I am missing when it comes to getting users from Membership Provider? Please let me know if I need to supply more detail.

The tables were restored to the new server from the other one, if that makes a difference.

Here is the execution plan! https://www.brentozar.com/pastetheplan/?id=HkNBZyp_U

Actual execution plan (script as -> execute to): https://www.brentozar.com/pastetheplan/?id=rykOgPuYL

Actual execution plan with @applicationname (execute -> entered value) https://www.brentozar.com/pastetheplan/?id=BkQyYOOF8

The execution plan you provided shows the INSERT INTO...SELECT portion of the procedure running in 19 ms.  The SELECT portion, which produces the actual results, ran in 1 ms.

The specific parameters didn't produce any results, but that's because the @PageSize and @PageIndex parameters were NULL.

Even if you included all the users, the data suggests this procedure would still run in less than a second on these (relatively, for SQL Server) small tables.

All that is to say that the execution plan suggests that this is a problem outside of SQL Server.

It could be that the application has changed, and is running more queries than expected when that page loads (and this is just one of them).  Or there is significant latency between the app server and the new Azure VM, among probably many other potential issues.

You'll need to work with the application developers to try and narrow down what, exactly, is slow when this page loads.  It does not appear that SQL Serve is taking minutes to produce this list of users.


Grouping Data Based on a Header Record I have a spreadsheet I'm importing into the database raw, a SQL Server 2016 database.  It has records in what is known as the "Lockbox format", used by banks apparently.  It looks something like this generically:

Batch1 details
child record
another child record
summary for batch
Header Row for Batch2

I have a fiddle set up here.

What's important to note is:

Column 1 indicates the "type" of record the row is.  It's the only "universal" column.
Every other column changes in nature based on the type of record.
The only thing tying child records to a parent is that they appear after a parent and before the next parent.  ID's are not shared between rows.
There are a variable number of child rows following a batch (not predictable).

Is there a way to query this so that I can easily group e.g. a batch and it's child + footer records?  One of the things I have to do is indicate that the footer records contain the right aggregates (taken from the child records; including the # of child records for a batch, which is part of what I need to verify).

Assuming the ordering of your input data is such that the beginning of the batch (Column1=1) always has the least RowNumber within that batch, you can do something like this to assign pseudo batch numbers using the largest RowNumber among the preceding header records:

select t.*,
    max(case column1 when 1 then RowNumber end)
    over(order by rownumber range between unbounded preceding and current row) as batchid
from tbl t

(fiddle)

From there you can use aggregate or window functions to calculate aggregates if you wrap the above in a subselect and use the pseudo batch numbers to partition data or group data:

select
max(case column1 when 1 then column3 end) as batchid,
sum(case column1 when 2 then some_numeric_column end) as sum_children,
max(case column1 when 8 then column3 end) as footer_value
from (
  select t.*,
    max(case column1 when 1 then rownumber end)
    over(order by rownumber range between unbounded preceding and current row) as batchnum
from tbl
) t1
group by batchnum


quoted identifiers on filtered indices I´ve 2 questions. I am using a T-SQL Server 2014.

When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so?

I´ve some SPs which have set quoted_identifiers to OFF, my question is, if I call the SP with QUOTED_IDENTIFIER ON, is it possible to inject these with values which have doublequotes inside?

The SP makes an insert and just uses the parameters. As I understand it, it should not take an effect if QUOTED_IDENTIFIER is set to ON or OFF.

This is my first SP which calls the second:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DECLARE @PreviousVersion bigint

IF @EntryTime IS NULL
BEGIN
    SET @EntryTime = GETUTCDATE()
END

IF @ID = 0 BEGIN
    EXEC dbo.GetNextDataID 'Object', @ID OUTPUT
    SET @PreviousVersion = 0
END
ELSE BEGIN
    SET @PreviousVersion = @Version
END

EXEC dbo.GetNextVersion 'Object', @Version OUTPUT

EXEC dbo.Insert_Object
    @ID,
    @Version,
    @PreviousVersion,
    @deleted,
    @Parent

The second looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT ON;

IF ISNULL(@ID, 0) = 0
BEGIN
    RAISERROR('Die ID ist 0 oder leer.', 11, 1)
    RETURN
END

IF ISNULL(@Version, 0) = 0
BEGIN
    RAISERROR('Die Version ist 0 oder leer.', 11, 2)
    RETURN
END

IF @EntryTime IS NULL
BEGIN
    SET @EntryTime = GETUTCDATE()
END

UPDATE dbo.ObjectTable
SET ID = @ID

INSERT INTO dbo.ObjectTable
            ( ID
            , Version
            , PreviousVersion
            , deleted
            , Parent)
        VALUES
            ( @ID
            , @Version
            , @PreviousVersion
            , @deleted
            , @Parent)

If I add the filtered index without setting QUOTED_IDENTIFIER to ON, it raises an error.

Parameter values containing double-quotes are not affected by the QUOTED_IDENTIFIER setting within the proc unless you use dynamic SQL. The setting affects how the T-SQL statements are parsed.

As long as the T-SQL proc code encloses string literals using ISO SQL standard single quotes like your example, you can safely recreate the proc from a session with QUOTED_IDENTIFIER ON to persist the setting as object meta-data. The QUOTED_IDENTIFIER ON setting will be used at run time and allow you to use filtered indexes and other SQL Server features that require the ISO standard setting.

I recommend you ensure both QUOTED_IDENTIFIER and ANSI_NULLS session settings are ON when creating creating objects to avoid surprises later.


How do I delete a user from the master database when the user has been deleted from the Server Logins using the SSMS Object Explorer I am using an Azure SQL Database. I deleted some users from a Azure SQL database after I saw them mentioned in a Vulnerability Assessment report. Here's how I deleted them:

Log in to SSMS
Expand the Object Explorer tree
Expand the 'Security' folder
Expand the 'Logins' folder
Highlight the user
Right-click and select 'Delete'

I went back to the Vulnerability Assessment blade and ran a new 'Scan' but the users I deleted still show up in the list. The list included SQL code to show that my users still exist. I ran that code back in SSMS in the master database and confirmed my users still exist. Here's the relevant code:
SELECT * FROM sys.database_principals
When I run the following code, I get an error saying the user does not exist ('or you do not have permission' - but I am a server admin so I ruled that out):
DROP LOGIN <username>
Note: I already deleted the users from all databases on the server.
How do I get rid of these logins?

There's two concepts with security at play here, and in general how SQL Server works. There are Logins and Users, which the differences are discussed a little further in this StackOverflow answer. You've deleted the Logins which is server level, but the Users associated to those Logins still exist at the database level, which has its own Security node you can expand and then a Users node below it.
Whenever you map a Login to a database (to grant database permissions) it creates a User in that database.
Example:


How to reduce identity values to avoid integer overflow? I have some leaf tables (no FK towards them) with a few hundreds of thousands records that are used to synchronize some external data using Entity Framework ORM. This involves some DELETEs followed by BULK INSERTs.

For most of the tables, some old values may stay forever, so I cannot use SEQUENCEs with CYCLE, as suggested in one of the comments.

One effect is that identity values will keep increasing over time and I want to be able to reduce their values.

This question and its answers explain that identity values cannot be updated, even if identity_insert is on for the table.

One quick way to do is transferring all data to a buffer table and perform the switch via rename. Something like the following:

-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready

begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.

select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0

DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)

insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak

drop table ActiveDirectoryCache_bak

alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';

select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.

-- this should be replaced with commit when not in test mode
rollback

I can afford to make this kind of operations during the night and they seem to take only a few seconds for my data volume (4 seconds for this example).

Question: Are there any methods to avoid performing a full data copy to obtain smaller values for identity columns? Or this is one of the best approaches in my context (reasonable data volume and being able to lock some tables for several seconds).

You could try simply dropping and re-adding the Identity column without copying the entire table. This has the same effect as what you are attempting with the code in the question. Neither approach really handles keeping existing rows as per the "some old values may stay forever" statement from the question, but for whatever situations work with the code in the question, the following should also work.

You should also consider starting the identity range at the min-value. Starting at 1 gives you just over 2.1 billion values, so starting at the minimum value (or close to it) gives you 4.2 billion values. Hence, less likely you will be needing such a reseed operation in the future.

SETUP

-- DROP TABLE dbo.IdentityTest;
CREATE TABLE dbo.IdentityTest
(
  ID INT IDENTITY(10203, 7) NOT NULL CONSTRAINT [PK_IdentityTest] PRIMARY KEY,
  SomethingElse UNIQUEIDENTIFIER NOT NULL,
  SomeName NVARCHAR(256) NOT NULL
);

INSERT INTO dbo.IdentityTest ([SomethingElse], [SomeName])
  SELECT NEWID(), so1.[name] + N'~' + so2.[name]
  FROM   master.sys.all_columns so1
  CROSS JOIN master.sys.objects so2;
-- 782,880 rows

SELECT * FROM dbo.IdentityTest;

Drop and re-Create Identity PK

BEGIN TRY
  BEGIN TRAN;

  ALTER TABLE dbo.IdentityTest
    DROP CONSTRAINT [PK_IdentityTest];

  ALTER TABLE dbo.IdentityTest
    DROP COLUMN [ID];

  ALTER TABLE dbo.IdentityTest
    ADD [ID] INT
    IDENTITY(-2140000000, 1)
    NOT NULL
    CONSTRAINT [PK_IdentityTest]
      PRIMARY KEY;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;
END CATCH;

SELECT * FROM dbo.IdentityTest;


Can you make SSL and SSL-only (hostssl) connections to PGPool? The new version of PGPool (3.2) has an "SSL" section in the conf file, and directions on how to use it in the manual. However, pool_hba.conf specifically says that PGPool doesn't support SSL (i.e. using "hostssl" to force ssl connectivity in the conf file). These seem contradictory. My company wants me to experiment with PGPool, but only if they have assurance that it will work with SSL forced always-on from the client side (from  psql/postgres drivers in our web applications).

This leads me to two questions:

Does PGPool 3.2 or newer support inbound SSL encrypted Postgres client connections?

Does PGPool 3.2 or newer support SSL encrypted connections to its backend Postgres instances?

Anecdotal experience or a reference to someone with it is preferred; the documentation seems spotty.

I'm not sure about pgPool, but pgBouncer (another connection pool) can use SSL by using Stunnel.

Check your functional requirements to see if pgBouncer can solve your problem. Maybe pgPool can also use Stunnel, you could give it a try.


Why does this tsvector update use so much space? This is my table structure:

  Column     |           Type           | Collation | Nullable |               Default                | Storage  | Stats target | Description
-------------+--------------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer                  |           | not null | nextval('tbl_disk_id_seq'::regclass) | plain    |              |
 inserttime  | timestamp with time zone |           | not null | now()                                | plain    |              |
 buildid     | text                     |           | not null |                                      | extended |              |
 app_version | text                     |           | not null |                                      | extended |              |
 device      | text                     |           | not null |                                      | extended |              |
 device_api  | text                     |           | not null |                                      | extended |              |
 userid      | text                     |           | not null |                                      | extended |              |
 path        | text                     |           | not null |                                      | extended |              |
 size        | numeric                  |           | not null |                                      | main     |              |
 type        | integer                  |           | not null |                                      | plain    |              |
 root        | integer                  |           | not null |                                      | plain    |              |
 status      | integer                  |           | not null | 0                                    | plain    |              |

I used this query to check the size of the table:

SELECT  relname AS "relation",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND relname like 'tbl_disk%'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

The result:

       relation        | total_size
-----------------------+------------
 tbl_disk_20180830     | 90 MB
 tbl_disk_20180831     | 32 MB

Now I add a new column document with type tsvector:

ALTER TABLE tbl_disk ADD "document" tsvector;

and update the data:

UPDATE tbl_disk
SET document = array_to_tsvector(ARRAY[split_part(path,'/',2),split_part(path,'/',6)]);

path data like : /data/user/0/com.abc.abc/database/olala.db

I just need two tokens 'data' and 'database' , that is why I split data.

After successfully updating, the size approximately doubles:

       relation        | total_size
-----------------------+------------
 tbl_disk_20180830     | 175 MB
 tbl_disk_20180831     | 61 MB

The data :

                          path                          |      document
--------------------------------------------------------+--------------------
 /data/user/0/com.abc.abc/lib/libabcqlite.so          | 'data' 'lib'
 /data/user/0/com.abc.abc/files/ddinfo2               | 'data' 'files'
 /data/user/0/com.abc.abc/lib/libabcsqlite.so         | 'data' 'lib'
 /data/user/0/com.abc.abc/lib/libabcsqlite.so         | 'data' 'lib'
 /data/user/0/com.abc.abc/shaders/b32                 | 'data' 'shaders'

You can see the size of tbl_disk_20180830 increases from 90MB to 175MB.

I don't know why it needs such a lot of space to save two tokens?

Databases cannot simply overwrite data "in place".  If they did, they wouldn't be able to have atomicity or isolation, and probably not much concurrency and durability either.

When you do a bulk update of every row in a table, in PostgreSQL, the resulting table has a copy of both the old data and the new data. You can rid of the copy of the old data by running VACUUM FULL <table name>.  Or you can just do a regular VACUUM, but if you do that it will not shrink the amount of space it takes up, it will just mark the space as being re-usable for future work on that table.


Merging Continous Date Ranges with upto 63 days in between I am trying to write a query that will take multiple date ranges and combine them into a contiguous date range. The catch is that I have to allow for up to 63 days in between the last termination date and the next effective date. I have found other queries that allow for one day in between but not multiple days.

DECLARE @ELIGIBILITY  TABLE
(
EFF_DT DATETIME,
TERM_DT DATETIME,
ID1 INT,
ID2 INT
)

INSERT INTO @ELIGIBILITY
(EFF_DT, TERM_DT, ID1, ID2) VALUES
--EFF_DT, TERM_DT, ID1, ID2
('25 MAY 1990','30 NOV 1994',18,36), -- 1 DAY GAP
('01 DEC 1994','31 DEC 1994',18,36), -- 1 DAY GAP
('01 JAN 1995','11 FEB 1995',18,36), -- 1 DAY GAP
('05 APR 1995', '31, DEC 1995',18,36), -- 53 DAY GAP
('16 JUN 2002','20 APR 2007',18,36), -- 2539 DAY GAP
('16 JUN 2002','20 APR 2007',12,3),
('21 JUL 2007','28 JUL 2007',12,3), -- 92 DAY GAP
('08 AUG 2007','31 AUG 2007',12,3), -- 11 DAY GAP
('11 OCT 2007','31 DEC 2007',12,3), -- 41 DAY GAP
('01 JAN 2008','07 JAN 2008',12,3), -- 1 DAY GAP
('14 JAN 2008','21 JAN 2008',12,3), -- 7 DAY GAP
('01 JUN 2008','30 SEP 2008',12,3), -- 5 MONTH GAP
('31 OCT 2008','30 NOV 2008',12,3), -- 30 DAY GAP
('01 JAN 2009','31 JAN 2009',12,3), -- 31 DAY GAP
('04 MAR 2009','31 MAR 2009',12,3), -- 32 DAY GAP
('03 MAY 2009','31 MAY 2009',12,3), -- 33 DAY GAP
('04 JUL 2009','31 JUL 2009',12,3)  -- 34 DAY GAP

EXPECTED RESULTS for 18,36

5/25/1990 - 12/31/1995
6/16/2002 - 4/20/2007

EXPECTED RESULTS for 12,3

6/16/2002 - 4/20/2007
7/21/2007 - 1/21/2008
6/1/2008 - 7/31/2009

WITH
CTE1 AS (SELECT LAG(TERM_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) PREV_DT,
                EFF_DT,
                TERM_DT,
                LEAD(EFF_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) NEXT_DT,
                ID1,
                ID2
         FROM ELIGIBILITY),
CTE2 AS (SELECT EFF_DT,
                TERM_DT,
                ID1,
                ID2,
                COALESCE(DATEDIFF( DAY, PREV_DT, EFF_DT), 1000) D1,
                COALESCE(DATEDIFF(DAY, TERM_DT, NEXT_DT), 1000) D2
         FROM CTE1
         WHERE COALESCE(DATEDIFF( DAY, PREV_DT, EFF_DT), 1000) > 63
            OR COALESCE(DATEDIFF(DAY, TERM_DT, NEXT_DT), 1000) > 63)
SELECT DISTINCT
       CASE WHEN D1 > 63
            THEN EFF_DT
            ELSE LAG(EFF_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT)
            END EFF_DT,
       CASE WHEN D2 > 63
            THEN TERM_DT
            ELSE LEAD(TERM_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT)
            END TERM_DT,
       ID1,
       ID2
FROM CTE2
ORDER BY ID1, ID2, EFF_DT

fiddle


Store the result from a cursor with exec into a temp table without explicitly named columns I need to execute a query in each db I have got on my server and the result needs to be stored into a temp table. Due to the query model, I can't explicitly set column names for temp table. I've tried the below query but get "Invalid object name '#tmp'" error (temp table is not available outside the cursor loop). When I change #tmp to global ##tmp I get error "There is already an object named '##tmp' in the database".

Any idea how to bypass it in a simple way without openrowset?

DECLARE
@db VARCHAR(50),
@query VARCHAR(MAX),
@sql VARCHAR(MAX)

SET @query = 'SELECT Col1=1, Col2=2 INTO #tmp'

DECLARE db_cursor CURSOR FOR
SELECT db=name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql =' USE '+@db+'; ' +@query+''
    FETCH NEXT FROM db_cursor INTO @db
EXEC (@sql)
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #tmp

I assume that the query is a constant query. In that case, use this slightly modified code, using the global temp table theme;

DECLARE
    @db VARCHAR(50),
    @query VARCHAR(MAX),
    @query2 varchar(max),
    @sql VARCHAR(MAX),
    -- make a unique global temp table name for now. Assumes that you drop
    -- the table explicitly at the end of each run.
    @tmpTbl varchar(50) = '##tmp_' + CAST(@@SPID as varchar)

SET @query = 'SELECT Col1=1, Col2=2 INTO ' + @tmpTbl;
SET @query2 = 'INSERT INTO ' + @tmpTbl + ';'

DECLARE db_cursor CURSOR FOR
SELECT db=name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql =' USE '+@db+'; ' +@query+''
    FETCH NEXT FROM db_cursor INTO @db
    EXEC (@sql)
    SET @query = @Query2; -- This will happen each time, but only
        -- adds a tiny bit of overhead to the process.
END

CLOSE db_cursor
DEALLOCATE db_cursor

SET @SQL = 'SELECT * FROM ' + @tmpSQL
EXEC (@sql)
SET @SQL = 'DROP TABLE ' + @tmpSQL
EXEC (@SQL)


"TASK MANAGER" process is taking over a single-user mode database. What is it? This is a duplicate of the question I asked on stackoverflow, but I was advised, that someone here could have a better idea what is happening.

I have a sporadic problem, when upgrading SQL Server in a single-user mode, using .NET SqlConnection, some other application somehow logs into the database, while the SQL code is being executed, and kicks my process out. SqlConnection is not closed or disposed in any way. But some other application somehow ends up connected to the database and that kicks my connection out.

When I run sp_who, I could see that a process that took control of the database is Command="TASK MANAGER".

Anyone could tell me what is this process, what is it's purpose, and how in the world it could get into a database, which is in single-user mode, and there is an active connection?

Had the same issue today, If you have not put your AUTO_UPDATE_STATISTICS ASYNC OFF, you will not be able to enter your database, you can solve this by putting your database offline. Important to know is that you need to set your deadlock priority to high, otherwise you will be deadlocked out of the command. Use following commands to get out of SINGLE user mode

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET ONLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE


Any different guidance for tempdb with Availability Groups? I have an Availability Group set up on SQL Server 2014 running on a two-node Windows Server Failover Cluster. The setup consists of two stand-alone instances + synchronous with automatic failover.

Many Microsoft articles I've read advocate using multiple files for TempDB to increase performance. It seems they recommend using 8 files.

Should I do that in the case of a this configuration? Will it improve performance?

You pretty much always want more than a single TempDB file.  I'd certainly not go above 8 files in most situations; and probably more like 4.  Having said that, the only way to know the best configuration is to test it.

This applies equally to SQL Server instances of all kinds, including high availability setups, not just AlwaysOn Availability Groups.


If PostgreSQL starts after copying data, is the data ok? I'm copying the data for a PostgreSQL database, that is, /var/lib/postgresql, from one server to another. The servers are identical except for the fact that one is 32bit and the other one is 64. I already copied the data once and PostgreSQL started, but, is there a way to verify the integrity of the data?

It is not safe to assume that the data is OK just because PostgreSQL starts. Pg does not do any kind of comprehensive verification run on the DB contents at startup. If it did it could take hours (or days or weeks for bigger DBs) to start.

It doesn't have any verification tools. The argument is that these should not be needed if the data is managed correctly. I'm coming to question that argument in the wake of the 9.3.x (x < 4) multixact corruption bugs, but building verification tools isn't a small job.

If the 64-bit server is running 32-bit PostgreSQL binaries, and they're compiled with the same options, it'll be fine.

If the 64-bit server is running 64-bit PostgreSQL binaries then you didn't copy the datadir in place, because 32-bit and 64-bit PostgreSQL data directories are not compatible and PostgreSQL will refuse to start.

My concern is about how you copied the data dir. You must stop the origin and destination PostgreSQL servers before copying if you just do a simple file system level copy. To live copy you must use one of the techniques documented in the manual - pg_basebackup, pg_start_backup() + rsync + pg_stop_backup() + WAL archiving, etc.

If you copied the datadir without stopping the server it might appear to be copied correctly, but it's not safe to use and may be subtly or not so subtly corrupt.


How to capture Query Status(Sleeping, Background, Running) in SQL Server Profile trace Is there any EventClass/Data Column which can log the Query Status to trace in SQL Server Profiler?

Query Status which we find through sys.dm_exec_requests

Profiler does not capture point in time information (ie, what is the status of this query or wait times/types or current blocks, however you can get deadlock events). But rather, it captures activity/event information (ie, this activity/event occurred so record it). As such, it will not be able to give you that kind of information since it is always runnable when those activities are occurring.


Extracting data from SQL Server linked server I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database.

In SQL Server management studio, I can see the linked server. I can browse the database on the server. I can see all tables in database.

When I try and query a linked table

select * from openquery(somedatabase,'select top 4 * from test')

I get error:

  The OLE DB provider "MSDASQL" for linked server "somedatabase" indicates that either the object has no columns or the current user does not have permissions on that object.

When I try to script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get same error.

Any help would be greatly appreciated.

I referred to following posts but it did not help.

http://www.sqlservercentral.com/Forums/Topic1036585-391-1.aspx

Error Querying MySQL server linked to SQL Server 2008

http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server#gsc.tab=0

Thanks.

Check the link server setup and see HOW the users connect to the link server. This can be done by going to Server Objects in SSMS -> Linked Servers -> [Link server name] ->  right click then select Properties -> security tab. It can be done without a security context, using the current login's security context, or it can be done using a specific login. Find out which account is being used and then make sure that account has access to the table on the linked server.


JOINing is done before COUNTing.  That is, it gets 5 (or whatever) rows after joining, but 5 is not the value you wanted.

Instead, use a subquery to get the COUNT(*).


How to compute a membership expiration date on SQL This is a follow up to this previously posted question. I'm using LibreOffice Base to work with some membership data. Further expansion might be possible in the future, but for now, the embedded database is fine, and it provides portability (I could send the file to someone and they can see the data, without haing to connect to an SQL server). The downside: LibreOffice Base uses HSQLDB version 1.8 (from circa 2008). Hence, the available functions are limited.

I have several tables and forms and queries to add and retrieve data from my database, including payments. Among the payments, my culprit are the membership renewal. There are several types of membership, each with different prices, and in some cases, different expiration periods. Members can purchase membership for 1 year or 3 years, and they could do it at any given point in time. Each membership expire on December 31st of the calendar year.

I have a table (actually a View produced by a filtering Query) called "membershipPayment" where I have

memberID paymentID paymentDate expirePeriod
0        0         2012-02-02  1
0        10        2013-02-15  3
0        30        2014-01-10  1
0        60        2015-08-15  3
1        5         2012-03-12  1
1        15        2013-04-01  1
1        20        2013-10-01  3
1        35        2014-02-01  1

The expirePeriod is the number of years of membership associated with that payment. Notice that in the example, in some cases members are renewing membership when their membership is still active due to a previous payment. I need to query this table/view to summarize these payments and have an expiration date and membership status, with one line per member

memberID expirationDate memberStatus
0        2019-12-31     active
1        2017-12-31     inactive

The memberStatus column should be computed from the expirationDate and CURRENT_DATE. No problems here. My issue is how to compute the expirationDate. I would like to use only a Query, if possible. I don't know how to loop (or if it's even possible to do such thing) along the rows of the "membershipPayment" table to increment the expirationDate based on the value of expirePeriod, or to keep track of a value from a previous row while checking another. I managed to compute the total number of membership purchased on a sigle year using SELECT, SUM() and GROUP BY, according to the answer of the previously cited post. But keeping track of the expirationDate, still no clue.

If macros are required, any help or advice is appreciated.

Thanks for any advice or comment, even if it's to point that my question is trivial,

All that you have to do is to add the ExpirationPeriod to the Year of payment and add 12.31 as the date of expiration.

If someone pay 2014.05.07 for 3 years then his membership expired at 2014+3.12.31


Unable to get the DBMS Logs in Oracle SQl Developer I am using Oracle SQL Developer (Version 18.4.0.376) and Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production "CORE  10.2.0.1.0 Production" TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production.

In the SQL Query Builder the output of DBMS_OUTPUT.PUT_LINE is not disabled automatically.

See the following screen shot:

Can any one suggest to me, how I can get rid of this issue?

This works just fine:

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('Test);
END;

Then your output will look like this:

PL/SQL procedure successfully completed.
Test

Reference: DBMS_OUTPUT (Oracle | Docs)

If you want to get rid of the PL/SQL procedure successfully completed message, then you can turn if off with the command SET FEEDBACK OFF. The whole thing will then look like this:

SET FEEDBACK OFF
SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('Test');
END;

Your output will then be:

Test

Reference: How do I Suppress “PL/SQL procedure successfully completed” message in sqlplus? (Stack Overflow)
Reference: SQLPlus® User's Guide and Reference (Oracle | Help Center)*


How to export an image column to files in SQL Server? I will migrate from a database. There is one column of type image that I would like to export to binary files on the file system. One file for each record. How can I do this with SQL Server?

This is the solution I came up with:

Enable xp_cmdshell with

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

If needed create a directory with xp_cmdshell to get the needed permissions.

EXEC master..xp_cmdshell 'mkdir C:\exportdir'

Use BCP with queryout

EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'

**your_db must be the fully qualified table name, i.e [Yourdb].[YourSchema].[YourTable]


Restored DB Server Using FQDN Causing Distribution Issues We had a complete RAID failure on one of our SQL Server 2008 R2 SP1 servers.  I setup a new server and restored everything and we're basically up and running again. But, I have one small issue: I am trying to restore the publications/subscriptions and I keep running in to errors with the server name.

It seems in my haste to get things back up and running I used the FQDN instead of the Netbios name somewhere.  If I do SELECT @@SERVERNAME, I get the netbios name ('KAI') but if I do an sp_helpservers then I see the FQDN ('kai.caymanport.com') for both the name and networkname fields.

How can I correct this or can I correct this?  I already tried to do a sp_dropserver, sp_addserver 'KAI', LOCAL (which worked) but that didn't help.

1, once your run the sp_Drop/addserver, most likely the system needs a start up if there is no other issues. You may also put the downtime into your consideration.

2, If the problem is still there after restarting your server, you may disable the connection between your replica serves, restore each one individually, then set the replication on your new server .

3, You may also experience issues if your SP is old, refer to Microsoft for SP new release.

Thank you,


logging in Bulk logged recovery VS Full recovery model I am trying to understand the logging aspects in Full and Bulk recovery models.As per MSDN ,select * into test from abcsd is logged minimally in Bulk logged ,but in full recovery model,it is logged fully.But while reading transaction log i am seeing different behaviour,Can please some one shed some light on this.

Database recovery model:

TLOG:

From the above snip we can see even though DB is in Full recovery model,only extent allocations are logged.Is this expected.I am seeing the same behaviour for Bulk logged recovery model too with only change being database pages are also backed up.If only extents are logged in full recovery model,how point in time restore capability is acheived.Please let me know if i am missing any thing

I finally found the answer Red gate transaction log management book by Tony and Gail shaw.

Below is the Snip for a select into operation done in full recovery model:

From the above snip we can see all the operations done to sometable are logged under the context of lop_format_page and logrecordlength is also 8276 bytes which indicates SQl eventhough under full recovery model,doesnt log each and every row,rather it logs entire image of the page plus log headers.

Below is the Snip for the same operation done in Bulk logged recovery model:

From the above Snip,we can see now there is no record indicating LOP_FORMAT_PAGE,rather Sql is now tracking only page allocations and extent allocations,plus some internal system table changes,but no data pages are present in the log.

Thanks to redgate for the amazing Book


Can I get SSMS to show me the Actual query costs in the Execution plan pane? I'm fixing performance issues on a multistatement stored procedure in SQL Server. I want to know which part(s) I should spend time on.

I understand from How do I read Query Cost, and is it always a percentage? that even when SSMS is told to Include Actual Execution Plan, the "Query cost (relative to the batch)" figures is still based on cost estimates, which can be far off actuals

I understand from Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken” that I can surround invocation of the stored procedure with SET STATISTICS TIME statements, and I will then get a list like this in the Messages pane:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

[etc]

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 206 ms.

with one output message for each statement.

I can 'easily' (though not conveniently) associate the time statistics output with the statement-by-statement execution plans in the Execution plan pane, by counting them: The fourth SQL Server Execution Times message output corresponds to Query 4 in the Execution plan pane, and so on.

But is there a better way?

I don't know of a way to do this in the plan from Management Studio, but this is one of the many things the free SentryOne Plan Explorer will do for you when you generate an actual plan from within the tool - it includes all the runtime metrics per statement.


Remote queries and linked servers I have 2 servers with SQL Server 2008 R2: INT & CRM.

I need to run an SSIS package on INT, which uses data from both an INT database and a CRM database. It's painfully slow.

I tried this to test a simple query (on INT):

EXEC sp_addlinkedserver @server='CRM',
                        @srvproduct='',
                        @provider='SQLNCLI',
                        @datasrc='<IP of CRM>'

EXEC sp_addlinkedsrvlogin
                        @useself='FALSE',
                        @rmtsrvname='CRM',
                        @rmtuser='<username>',
                        @rmtpassword='<userpassword>'

I also gave <username> ddl_admin role on the CRM server, as recommended on this answer:
SQL performance issues with remote query across linked server

I then run this query:

UPDATE [CRM].DB.dbo.table1
SET field = 1
WHERE id = (SELECT id FROM [CRM].DB.dbo.table2 WHERE secondary_id = 9999)

This query takes 40 ms to run when on CRM itself. It takes 30 seconds to run in the above example. There are no results returned, it can't be network issues, so why does it take so long?

Okay so here's what I think you need to do. I'm assuming since you are already using integration services you have both of your data sources set so I'm not going into detail on that. If you have any questions as to how to properly set those up I can help you to. So the first thing you need to do is create a new package in your Integration services solution. Then you need to create a dataflow task. You will find that in your You toolbox like so .

Once you have done that open the DataFlowTask and Create a new OLEDB Connection. Once again found in the toolbox.

Open that Up and set up your SQL command this will run locally on the machine and will therefore suffer no linked query costs.

Next Set up your OLEDB command the portion that will execute a SQL command with the result Set. First Select OLEDB Command from the toolbox.

Connect your OLE Connection to You OLE Command.

Next you create a stored procedure to handle this update. Its a fairly simple procedure to make it will ultimately look like this.

CREATE PROCEDURE UPD_CRM_TableName(
    @ID int,
    @Field int)

UPDATE TableName SET
@Field = Field

WHERE @ID = ID

Then open you OLEDB command and select the appropriate connection in the connection tab.

Go to the Component properties tab click on SQL Command and the appropriate values to execute your stored procedure.

Then go to column mappings and make your parameters match you columns and you will be good to execute the package. Note that the update stored procedure will be sent to the server with the appropriate parameters required to update the table eliminating the need for your costly linked query.

Setting this up for any update you command will be incredibly easy to set up and will basically run as fast as your hardware and network can keep up. Make sure your integration services box has plenty of memory as that is where all of it's operations are executed. If you have any questions feel free to let me know.


EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other? Whenever I need to check for the existence of some row in a table, I tend to write always a condition like:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT *  -- This is what I normally write
          FROM another_table
         WHERE another_table.b = a_table.b
       )

Some other people write it like:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT 1   --- This nice '1' is what I have seen other people use
          FROM another_table
         WHERE another_table.b = a_table.b
       )

When the condition is NOT EXISTS instead of EXISTS: In some occasions, I might write it with a LEFT JOIN and an extra condition (sometimes called an antijoin):

SELECT a, b, c
  FROM a_table
       LEFT JOIN another_table ON another_table.b = a_table.b
 WHERE another_table.primary_key IS NULL

I try to avoid it because I think the meaning is less clear, specially when what is your primary_key is not that obvious, or when your primary key or your join condition is multi-column (and you can easily forget one of the columns). However, sometimes you maintain code written by somebody else... and it is just there.

Is there any difference (other than style) to use SELECT 1 instead of SELECT *?
Is there any corner case where it does not behave the same way?
Although what I wrote is (AFAIK) standard SQL: Is there such a difference for different databases / older versions?
Is there any advantage on explicity writing an antijoin?
Do contemporary planners/optimizers treat it differently from the NOT EXISTS clause?

No, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...) in all major DBMS. I've often seen (NOT) EXISTS (SELECT NULL ...) being used as well.

In some you can even write (NOT) EXISTS (SELECT 1/0 ...) and the result is the same - without any (division by zero) error, which proves that the expression there is not even evaluated.

About the LEFT JOIN / IS NULL antijoin method, a correction: this is equivalent to NOT EXISTS (SELECT ...).

In this case, NOT EXISTS vs LEFT JOIN / IS NULL, you may get different execution plans. In MySQL for example and mostly in older versions (before 5.7) the plans would be fairly similar but not identical. The optimizers of other DBMS (SQL Server, Oracle, Postgres, DB2) are - as far as I know - more or less capable of rewriting these 2 methods and considering the same plans for both. Still, there is no such guarantee and when doing optimization, it is good to check the plans from different equivalent rewrites as there could be cases that each optimizer doesn't rewrite (eg. complex queries, with many joins and/or derived tables / subqueries inside the subquery, where conditions from multiple tables, composite columns used in the joining conditions) or the optimizer choices and plans are affected differently by the available indexes, settings, etc.

Also note that USING cannot be used in all DBMS (SQL Server for example). The more common JOIN ... ON works everywhere.
And the columns needs to be prefixed with the table name/alias in the SELECT to avoid errors/ambiguities when we have joins.
I also usually prefer to put the joined column in the IS NULL check (although the PK or any non-nullable column would be OK, it might be useful for efficiency when the plan for LEFT JOIN uses a non-clustered index):

SELECT a_table.a, a_table.b, a_table.c
  FROM a_table
       LEFT JOIN another_table
           ON another_table.b = a_table.b
 WHERE another_table.b IS NULL ;

There is also a third method for antijoins, using NOT IN but this has different semantics (and results!) if the column of the inside table is nullable. It can be used though by excluding the rows with NULL, making the query equivalent to the previous 2 versions:

SELECT a, b, c
  FROM a_table
 WHERE a_table.b NOT IN
       (SELECT another_table.b
          FROM another_table
         WHERE another_table.b IS NOT NULL
       ) ;

This also usually yields similar plans in most DBMS.


Archive logs Pg_archive cleaner I have Postgres installed on windows 10. I have master-slave replication ( 1 pc is master and 2 other pc are slaves). I have archive logs saved on a file on master pc and slaves are connected to that file. That file has become quite big and i need to know how to remove log files with pg_archive cleaner. I also have automatic backup jobs on every day.

pg_archivecleanup won't work automatically with 2 replicas, as it doesn't coordinate between them to reach an agreement.  You would have to manually decide up to what point you can remove them by determining which replica has the older restartpoint.  You could then feed that oldest restartpoint to pg_archivecleanup, but I don't see the point, if you have to decide manually what to use, why not just remove them manually as well?
You could use replication slots, and do away with the archive.  Then it will automatically keep files in pg_wal and remove them once they are no longer needed by any replica.  This does mean pg_wal might need to have somewhat larger capacity than it currently does, if the replicas ever get way behind.  (But then you don't have to reserve capacity for the archive anymore)


Cassandra upgrade/repair issues in migration I wanted to migrate part of the column families in a cluster to a new cluster.

I made a snapshot of the source cluster and copied the data, made a new cluster with the same name, same number of nodes that all have the same tokens as their parallel in the original cluster.
I copied the data to the data directory, renamed the file names to match the schema in 1.1 (original cluster is 1.06).

After recreating the schema in the CLI, and upgrading sstables no nodes own any of the data (effective ownership 0.00), any idea what I am doing wrong here?

It turns out that the original cluster had different DC and rack names in it's topology, I changed the names and copied the data again and it works.


How do I get column name from a SQL statement? Is there any easy way just to get the column names returned from a SQL statement ?

Such as:

 SELECT *
 FROM ATable
 FULL OUTER JOIN BTable b ON a.SOME_KEY = b.SOME_KEY
 FULL OUTER JOIN CTable c ON b.SOME_ID = c.SOME_ID
 FULL OUTER JOIN DTable d ON a.SOME_OTHER_ID = d.SOME_OTHER_ID
 WHERE a.SOME_KEY = @key AND c.SOME_SERIAL_NR = @serialnumber

I cannot seem to get

select column_name
from information_schema.columns
where table_name = 'table'

to work in my current context

I prefer this query, for a number of reasons:

SELECT
  [table]  = s.name + N'.' + t.name,
  [column] = c.name
FROM sys.columns AS c
INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
WHERE t.name IN (N'ATable', N'BTable', N'CTable', N'DTable');

You can tell which column comes from which table
You can see the schema (schema is important)
You avoid the broken and incomplete INFORMATION_SCHEMA views - this doesn't cause a problem for this particular query, but there will be cases where you can't rely on metadata from INFORMATION_SCHEMA
You should be in the habit of always using the N prefix on string literals when the underlying data type in nchar/nvarchar.
You should also get into the habit of using proper statement terminators

The cons with my solution:

It takes a few seconds longer to type
It won't run on Oracle, MySQL, Postgres, etc.


Which columns should be indexed when all may be used in different search queries? Background

I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, which means I have to have a column in certain tables which holds the ID of the city that each row belongs to.

Right now I have three different tables:

Cinemas - Contains each city's cinema (ID and name).
Movies - Contains all movies that has been/will be shown at the cinema.
Showtimes - Contains all showtimes for all movies in all cities.

The structure of the Showtimes table is the following:

Column Name   | Column Type  | Description
--------------+--------------+---------------
ID            | BIGINT       | (Primary) Unique ID for each showtime (perhaps unnecessary?)
CinemaID      | TINYINT      | Foreign key bound to Cinemas.ID
MovieID       | BIGINT       | Foreign key bound to Movies.ID
Showtime      | DATETIME     | At what date and time the movie will show

(will contain multiple rows for each movie, i.e. one row for each showtime)

How this table will be used

A user of the website must be able to:

View all current/upcoming movies and showtimes (sorted by date) in the selected city.

Example query (backend):

SELECT MovieID, Showtime FROM Showtimes WHERE CinemaID = ? ORDER BY Showtime

Select a single movie and view all showtimes for that specific title only (in the selected city).

Example query:

SELECT Showtime FROM Showtimes WHERE CinemaID = ? AND MovieID = ? ORDER BY Showtime

Select a single day and view all movies and showtimes for that day only (in the selected city).

Example query:

SELECT MovieID, Showtime FROM Showtimes WHERE CinemaID = ? AND (Showtime BETWEEN [date 12:00 AM] AND [date 12:00 PM])

So naturally I decided that I needed to create indexes for the columns.

Problem

What I'm having trouble with is deciding/determining how to index the columns properly. One index for each column seems quite expensive[1] [2] so I started looking into composite indexes, which seems to be the right choice but also led to even more confusion.

From my understanding (based on what I've read) you should add the columns to the index by order of selectivity, making the most selective (I'm guessing that means the most unique/with the most cardinality?) column the first in the composite index[3] (in my case that would be the Showtime column). The only problem with that is that the index can only be used by the database if the first column is included in the search query[4] [5], which it currently isn't in either of my queries.

Question

What kind of index(es) should I apply to my columns in order to cover all usage scenarios? (the last scenario may be omitted, but the first two are required)

Should I use a composite index on all columns, for some columns, or do I need a separate index for each column?

This table is updated at most a few times per week to add new showtimes.

Footnotes

1 MySQL indexes - what are the best practices?

2 Indexing every column in a table

3 How important is the order of columns in indexes? (question)

4 How important is the order of columns in indexes? (#2 top-voted answer)

5 When should I use a composite index?

Composite Primary Key

I would define the primary key as a composite key of (CinemaID, MovieID, Showtime).

These 3 columns uniquely identify each row, and so having a separate ID column is not necessary.

Composite (Secondary) Index

With this PK, the only additional index you will need for your queries is (CinemaID, Showtime).

Why these indexes?

A good way to think about indexes are used is to think of them as the order of columns in a spreadsheet.

Imagine a spreadsheed with (CinemaID, MovieID, Showtime) that is sorted by each column successively.

All your queries have CinemaID present, which means you can quickly find the "section" of the spreadsheet for that CinemaID. Then, for your query that searches by MovieID, you can easily find the "subsection" in the 2nd column, where MovieID matches the searched for value.

As the 3rd column of Showtime is also sorted, you can imagine how quickly and easily it is to find all those show times for that movie, in that cinema. The DBMS does things in a similar way and can retrieve those results extremely quickly.

As for your other queries, they all start with CinemaID and then use Showtime in some manner. They also need the MovieID in their results.

So, the (CinemaID, Showtime) index has you covered there. Again, the CinemaID easily finds the "section" of the spreadsheet (in the analogy), and all possible showtimes (and there are going to be duplicates, assuming there is more than one screen) will be listed in order and are easily searched and/or sorted by those values.

Even better, since your primary key includes MovieID, that column is included for all secondary indexes after the defined columns (at least for MySQL InnoDB - others engines too, but not necessarily all.)

Think of that is a 3rd column in the "spreadsheet" of our secondary index. The reason the column exists is to have all parts of the primary key available to do a lookup to the main table (aka the clustered index, in InnoDB), if necessary. In this simple case, no lookup is needed, so it is even more efficient as it doesn't require that double lookup.

Using just this primary key and a single secondary index, you should obtain excellent performance on any of the queries you listed.

Afterthought

My assumption that this uniquely identifies each row may NOT be correct if you have a movie showing on multiple screens at the same time. If you want to be able to separately identify those screens, then my solution is not best (I can provide another solution for that situation, please just let me know.)


upload data from on-premises folder to Azure DB as the server admin is SQL authenticated user I have azure SQL DB, with SQL authenticated user as server admin, we have a requirement to upload data from on-premises folder to Azure DB. How can we achieve this?
1.SQL authenticated user is not recognized in AD
2.is there any service account other than this server admin to az-sql-dbs?

You can use Azure SSIS integration with Azure Data Factory as explained here. On this resource you will find how to run packages that use Windows Authentication to connect to on-premises resources like a folder.

Hope this helps.


Why Is oracle ignoring my index? This query is not too complex:

select
    trunc(created_date, 'MONTH') as created_date,
    al.op_name,
    al.region,
    al.alarm_type,
     COUNT(1) as total_new,
     SUM(
        (SELECT
            COUNT(1)
         from alarm_table ial
            WHERE ial.status_alarm = 'SOLVED'
            AND TRUNC(ial.solved_date, 'MONTH') = TRUNC(al.created_date, 'MONTH')
            AND ial.region = al.region
            AND ial.op_name = al.op_name
            AND ial.alarm_type = al.alarm_type)
    ) as total_solved
FROM
    alarm_table al
WHERE
    created_date is not null
group by
    trunc(al.created_date, 'MONTH'),
    al.op_name,
    al.region,
    al.alarm_type
order by trunc(al.created_date, 'MONTH') desc

And here is its explain plan:

Plan
SELECT STATEMENT  ALL_ROWSCost: 5,497
    3 SORT AGGREGATE  Bytes: 51  Cardinality: 1
        2 TABLE ACCESS BY INDEX ROWID TABLE USER.alarm_table Cost: 22  Bytes: 51  Cardinality: 1
            1 INDEX RANGE SCAN INDEX USER.IDX_alarm_table_RESU Cost: 3  Cardinality: 73
    6 SORT ORDER BY  Cost: 5,497  Bytes: 1,055,263  Cardinality: 24,541
        5 HASH GROUP BY  Cost: 5,497  Bytes: 1,055,263  Cardinality: 24,541
            4 TABLE ACCESS FULL TABLE USER.alarm_table Cost: 3,025  Bytes: 11,696,387  Cardinality: 272,009

The Question is: I have an index containing the columns al.op_name, al.region and al.alarm_type (that IDX_alarm_table_RESU, which it even uses on the first part).

Since there is no further joins, why would it be doing the FTS?

The outer select needs to find all rows in your table that match the where clause created_date is not null. That column isn't in that index, so the index (statistically) doesn't help – doing a full table scan is usually much faster than accessing all rows via an index.

If you had a separate index that could be used for that clause, it still might not be if it is not selective enough (i.e. if you don't have a vast majority of rows with null create dates).

If you can extend that index, try adding trunc(created_date, 'MONTH') to it and changing the (outer) where clause to use that function too (the null will "propagate" through the function).
Then you can try adding the status column too to avoid the table completely.

Indexing the function doesn't appear to work here, but indexing the date should help for the outer query.

But looking at the query more closely, I don't understand the meaning of the sum(select count(1) ...) in the select list, which is the most costly part. I don't believe this will return anything meaningful (count of rows per group times number of rows with status='SOLVED' per group).
If you want the number of rows that have status_alarm = 'SOLVED' within each group (this makes sense to me), you could rather:

sum(case when status_alarm = 'SOLVED' then 1 else 0 end) as total_solved

With that and a covering index, the query is answered with a single index fast full scan.

Test setup

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>
SQL> drop table foo;

Table dropped.

SQL> create table foo(
  2      cd date
  3    , p1 number not null
  4    , p2 number not null
  5    , p3 number not null
  6    , st number
  7    , stuff char(250));

Table created.

SQL>
SQL> insert /*+ append */ into foo
  2    select
  3     case when mod(rownum,20) = 0 then null else sysdate-(rownum/10000) end td
  4    ,round(dbms_random.value(0, 5)) p1
  5    ,round(dbms_random.value(0, 5)) p2
  6    ,round(dbms_random.value(0, 5)) p3
  7    ,round(dbms_random.value(0, 4)) st
  8    ,'lets imagine there are lots more columns in the table'
  9    from dual connect by level <= 500000;

500000 rows created.

SQL> commit;

Commit complete.

SQL> create index foo_ix1 on foo(p1, p2, p3);

Index created.

SQL> create index foo_ix2 on foo(cd, p1, p2, p3, st);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

Query structured the same as yours

SQL>
SQL> set timing on
SQL> set autotrace traceonly
SQL> select
  2    trunc(cd, 'MONTH') cm
  3    ,p1
  4    ,p2
  5    ,p3
  6    ,count(1) as oc
  7    ,sum(
  8      (select count(1)
  9       from foo b
 10       where b.st = 0
 11       and trunc(b.cd, 'MONTH') = trunc(a.cd, 'MONTH')
 12       and b.p1 = a.p1
 13       and b.p2 = a.p2
 14       and b.p3 = b.p3)
 15    ) as st
 16  from
 17    foo a
 18  where
 19    trunc(cd, 'MONTH') is not null
 20  group by
 21    trunc(cd, 'MONTH')
 22    ,p1
 23    ,p2
 24    ,p3
 25  order by
 26    trunc(cd, 'MONTH');

648 rows selected.

Elapsed: 00:04:15.83

Execution Plan
----------------------------------------------------------
Plan hash value: 1720639959

------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |   475K|  7885K|   |  5901   (3)| 00:01:11 |
|   1 |  SORT AGGREGATE        |     |     1 |    20 |   |        |      |
|*  2 |   INDEX FAST FULL SCAN | FOO_IX2 |    28 |   560 |   |   583   (2)| 00:00:07 |
|   3 |  SORT ORDER BY         |     |   475K|  7885K|    14M|  5901   (3)| 00:01:11 |
|   4 |   HASH GROUP BY        |     |   475K|  7885K|    14M|  5901   (3)| 00:01:11 |
|*  5 |    INDEX FAST FULL SCAN| FOO_IX2 |   475K|  7885K|   |   614   (7)| 00:00:08 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."P1"=:B1 AND "B"."P2"=:B2 AND "B"."ST"=0 AND
          TRUNC(INTERNAL_FUNCTION("B"."CD"),'fmmonth')=:B3)
   5 - filter(TRUNC(INTERNAL_FUNCTION("CD"),'fmmonth') IS NOT NULL)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   22699250  consistent gets
      3  physical reads
      0  redo size
      22461  bytes sent via SQL*Net to client
    996  bytes received via SQL*Net from client
     45  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    648  rows processed

That's a lot of gets.

Modified query

SQL>
SQL> select
  2    trunc(cd, 'MONTH') cm
  3    ,p1
  4    ,p2
  5    ,p3
  6    ,count(1) as oc
  7    ,sum(case when st = 0 then 1 else 0 end) as sc
  8  from
  9    foo a
 10  where
 11    trunc(cd, 'MONTH') is not null
 12  group by
 13    trunc(cd, 'MONTH')
 14    ,p1
 15    ,p2
 16    ,p3
 17  order by
 18    trunc(cd, 'MONTH');

648 rows selected.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 4221217160

------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |   475K|  9277K|   |  6438   (2)| 00:01:18 |
|   1 |  SORT ORDER BY         |     |   475K|  9277K|    16M|  6438   (2)| 00:01:18 |
|   2 |   HASH GROUP BY        |     |   475K|  9277K|    16M|  6438   (2)| 00:01:18 |
|*  3 |    INDEX FAST FULL SCAN| FOO_IX2 |   475K|  9277K|   |   614   (7)| 00:00:08 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TRUNC(INTERNAL_FUNCTION("CD"),'fmmonth') IS NOT NULL)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
       2125  consistent gets
      0  physical reads
      0  redo size
      21304  bytes sent via SQL*Net to client
    996  bytes received via SQL*Net from client
     45  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    648  rows processed

(Note that even without any index, this version is orders of magnitude more efficient than the first one.)


What is the best way to reduce the size of ibdata in mysql? I have some Production servers whose ibdata files increase in size day by day.

It has already consumed 290GB of space.

The tables in the servers are mostly InnoDB and there are high read and write requests.

The log file size also increasing. There is a huge amount of Data in the Tables.

How can I control the growing size of both?

I am not using innodb_file_per_table.

Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1

This file normally houses many classes of information (when innodb_file_per_table is 0)

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data

Rollbacks Segments
Undo Tablespace

Table Metadata
See Pictorial Representation

Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.

Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:

Makes the table's data and indexes contiguous inside ibdata1
It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table.
To shrink ibdata1 once and for all you must do the following

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) (More details here)

Step 02) Drop all databases (except mysql, performance_schema, and information_schema)

Step 03) Shutdown mysql

Step 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

If you would like to see how much actual data is stored in MyISAM and InnoDB, please run this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;