MySQL 5.7: deprecated partitioning method warning error I have here a couple of MySQL DB tables optimised with partitioning as in:

ALTER TABLE radpostauth PARTITION BY HASH(id) PARTITIONS 32;

With the upgrade do MySQL 5.7, the following warning started appearing in the error logs:

The partition engine, used by table 'y.radpostauth', is deprecated and
will be removed in a future release. Please use native partitioning instead.

I have an idea I can do an ALTER TABLE REBUILD however, no clue about what to do to make it compliant to 5.7 and beyond.

What do you advise?

As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is removed in MySQL 8.0, when the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do.

Use of tables with nonnative partitioning results in such warning written in error log.The MySQL server checks tables that using nonnative partitioning at startup.

To disable this check, use the --disable-partition-engine-check option.

Tables with nonnative partitioning should be changed to use an engine that provides native partitioning as shown below.

ALTER TABLE table_name ENGINE = INNODB;

Reference:
Partitioning


Mixed Editions in a 3 Node SQL Server Always on Availability Group I could not find any solid documentation anywhere, checking any of you have seen/built an AG that looks like below. Appreciate your responses.

SQL SERVER 2016

Primary - Enterprise edition
Secondary 1 - Enterprise edition - with Sync Replication
Secondary 2 - Standard Edition with Async Replication

Thank you

You might missed that the Availability Groups are only available in Enterprise Edition.

In you scenario (with mixed edition), you would neither able to do following AG Configuration:

Availability Groups
Required all replicas running Enterprise Edition

Basic Availability groups (limitations)
It limit to two replicas only

Distributed Availability groups
Standard Edition only support Basic Availability Groups and Basic Availability Groups cannot be part of Distributed AGs


SSIS comparison load between new, updated and deleted records and populate a staging database So far,

My control flow looks like:

Execute SQL Task
- This executes a statement that creates a staging table. If one exists, it is dropped so everytime the package is run, the staging table will be able to track the updates that have occurred. My code for this is:

IF OBJECT_ID('CDC_Staging', 'U') IS NOT NULL
   DROP TABLE  CDC_Staging;

CREATE TABLE CDC_Staging
(
    [Employee_ID] [int] PRIMARY KEY NOT NULL,
    [FirstName] [nvarchar](255) NULL,
    [LastName] [nvarchar](255) NULL,
    [Education] [nvarchar](255) NULL,
    [Occupation] [nvarchar](255) NULL,
    [YearlyIncome] [float] NULL,
    [Sales] [float] NULL
);

Data Flow Task
- The OLE DB Source is the Source Database. The Lookup uses the results of this query:

SELECT [Employee_ID] FROM [CDC_Target]

Then I use a conditional split that puts inserts in the Target Database and Updates to Staging

My Final control flow task is an execute SQL Task. I used this query to update the target database.

UPDATE [dbo].[CDC_Target]
   SET [FirstName] = Staging.[FirstName]
      ,[LastName] = Staging.[LastName]
      ,[Education] = Staging.[Education]
      ,[Occupation] = Staging.[Occupation]
      ,[YearlyIncome] = Staging.[YearlyIncome]
      ,[Sales] = Staging.[Sales]
FROM [CDC_Target]
   INNER JOIN
     [CDC_Staging] AS Staging
     ON [CDC_Target].Employee_ID = Staging.Employee_ID

I just inserted 4 more records into my source table. After I ran the package, the Source Table and Target table had the correct data but the staging table did not contain the 4 inserts that I committed.

Am I doing something wrong here? Any suggestions on how to better do this?

PS. I followed a tutorial explaining how to do this, I'm a Jr. DBA but my team lead is wanting me to learn SSIS.

If I've understood correctly your OLE DB Destination is to the 'target' table and your OLE DB Destination 1 is to the 'staging' table? That would explain why your new rows aren't appearing in the 'staging' table as you're only sending them to the 'target' table.

If you want all rows (updated or inserted) to appear in the 'staging' table, I would suggest you remove the Lookup and Conditional Split and simply insert all rows to the 'staging' table. Then use a Execute SQL task to fire a MERGE statement to update or insert as appropriate to the 'target' table. In my experience this is much more performant that using the lookup to decide whether to insert or update


Normalization: 'zip code' as a primary key I've seen frequently that you should not have all address fields (zip, street, city, state, country, etc) in the same table as your user info table (pk_id, name, email... ).

This would be in violation of the 3rd normalization. That makes sense, all the address fields are not based on the user (or user's pk).

However, I have seen more than once that a zip code makes a good primary/foreign key to fix this? Huh? Example: Normalization of Database

Obviously more than one person can live in the same zip code so this does not make sense to me. It seems odd to me to keep the zip code in the user table but leave the rest of the address in another table.

What is the standard best way to have an address table separated, but still abiding by the rules of normalization?

The page you linked to is, quite simply, wrong.

It makes no sense for a ZIP code to be a primary key in an address table.  You would expect to have multiple addresses in the same ZIP code, as you said.  ZIP codes cross city, county, and state lines which makes it uniquely poor as a key (postal codes in other countries may not be as problematic but data modelers always have to worry about the worst case).  Plus, ZIP codes change over time for an address which is not something you want in your primary keys.

In general, given that addresses tend not to have anything that works as a natural primary key, your address table will need a synthetic primary key (address_id) that has no meaning and simply acts as a primary key.  Databases have different ways to generate synthetic primary keys, sequences and auto-incrementing columns are common approaches.


Find material for improvement of my SQL ability The following part that is needed to be improved is:

script
T-SQL
Procedure
Function

Problem:
Where can I get material to improve my ability in SQL coding? Is there any website for downloading a database with different task/assignment and solution?

Where should I go to review lots of SQL code?

If you are looking to just improve your SQL coding ability I would go to the following sites:

http://www.sqlservercentral.com - check out the scripts section
http://www.sqlcourse.com/index.html - this site has some good basic tutorials
http://www.sql-tutorial.net/ - also another good site for tutorials
http://www.mssqltips.com/ - covers a lot of different areas but still has good material
http://blog.sqlauthority.com/ - VERY knowledgeable fella. Just search his site for what you are looking for

Here is a list of sample SQL databases for you - http://msftdbprodsamples.codeplex.com/

For some FREE reading on check out - http://www.sqlservercentral.com/articles/books/67441/


Trying to count multiple values and total SELECT
  COUNT (DISTINCT competition.genre_id) genrecount,
  COUNT(DISCTINCT band.band_id),
  genre.name,
  genre.genre_id
FROM genre, competition_band, competition, band
WHERE
  genre.genre_id = competition.genre_id
  AND band.band_id = competition_band.band_id
  AND competition.competition_id = competition_band.competition_id
GROUP BY genre.genre_id, band.band_id, genre.name, competition.competition_id
ORDER BY name;

Basically I am trying to show how many competitions of each genre are being held and how many bands have entered competitions of each genre.

Really stumped, the results of this just show genrecount and band_id as a series of 1's and multiple genres.

can anyone help?

Band (band_id, band_name uni_id, band_leader)
COMPETITION (COMPETITION_ID, COMPETITION_TITLE, COMPETITION_DATE, START_TIME, SPONSOR_ID, GENRE_ID)
COMPETITION_BAND (BAND_ID, COMPETITION_ID, SET_TIME)
GENRE(GENRE_ID, NAME)

Do we need a sub query?

Try this for a count of competitions in each genre:

SELECT
  COUNT(competition.competition_id) as competition_count,
  genre.genre_id,
  genre.name
FROM
  genre
  INNER JOIN competition on ( genre.genre_id = competition.genre_id )
  INNER JOIN band on ( band.band_id = competition_band.band_id )
GROUP BY genre.genre_id, genre.name
ORDER BY genre.name;

A count of bands who have entered competitions sounds like a different query to me:

SELECT
  COUNT(band.band_id) as bands_in_competitions_count, --double counts bands in multiple competitions
  COUNT(distinct band.band_id) as bands_in_competitions_in_this_genre,
  genre.genre_id,
  genre.name
FROM
  genre
  INNER JOIN competition on ( genre.genre_id = competition.genre_id )
  INNER JOIN competition_band on ( competition.competition_id = competition_band.competition_id )
  INNER JOIN band on ( band.band_id = competition_band.band_id )
GROUP BY genre.genre_id, genre.name
ORDER BY genre.name;


Why is this explicit cast causing problems only with a Linked Server? I am querying data from a linked server through a view on the origin server.
The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn't have any suitable info. The columns are therefore explicitly cast to their respective types. I updated the view, changing a column from

NULL AS Modified

to

CAST(NULL as DateTime) as Modified

However, after performing this update, the view is triggering the following error message:

  Msg 7341, Level 16, State 2, Line 3
  Cannot get the current row value of column "(user generated expression).Expr1002" from OLE DB provider "SQLNCLI11" for linked server "".

We have done this "explicit cast"-change generally across the origin server without worries, and I suspect the issue might be related to the version of the servers involved. We don't really need to apply this cast, but it feels cleaner. Right now I'm just curious as to why this is happening.

Server Version (origin):

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Server Version (linked):

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Edit
I just realized I made a mistake by not posting all the columns in question, and I must apologize for leaving out an important detail. I don't know how I didn't notice this sooner. The question still remains, though.

The erroneous cast does not happen with the cast to DateTime, but with a column being cast to UniqueIdentifier.

This is the culprit:

CAST(NULL AS UniqueIdentifier) AS [GUID]

UniqueIdentifiers are supported on SQL Server 2008 R2, and as mentioned in the comments, the query performed by the view runs fine on the linked server.

So, I was able to reproduce the error after realizing that the CAST was being done locally, not on the remote instance. I had previously recommended moving up to SP3 in the hopes of fixing this (partially due to not being able to reproduce the error on SP3, and partially due to it being a good idea regardless). However, now that I can reproduce the error, it is clear that moving up to SP3, while still probably a good idea, is not going to fix this. And I also reproduced the error in SQL Server 2008 R2 RTM and 2014 SP1 (using a "loop-back" local Linked Server in all three cases).

It seems that this problem has to do with where the query is executing, or at least where part(s) of it are executing. I say this because I was able to get the CAST operation to work, but only by including a reference to a local DB object:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (SELECT TOP (1) 1 FROM [sys].[data_spaces]) tmp(dummy);

That actually works. But the following gets the original error:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (VALUES (1)) tmp(dummy);

I am guessing that when there are no local references, the entire query is shipped off to the remote system to be executed, and for some reason NULLs cannot be converted to UNIQUEIDENTIFIER, or perhaps the NULL is getting translated by the OLE DB driver incorrectly.

Based on the testing that I have done, this would appear to be a bug, but I'm not sure if the bug is within SQL Server or the SQL Server Native Client / OLEDB driver. However, the conversion error occurs within the OLEDB driver, and so is not necessarily an issue of converting from INT to UNIQUEIDENTIFIER (a conversion which is not allowed in SQL Server) since the driver is not using SQL Server to do conversions (SQL Server also does not allow for converting INT to DATE, yet the OLEDB driver handles that successfully, as shown in one of the tests).

I ran three tests. For the two that succeeded, I looked at the XML execution plans which show the query that is being executed remotely. For all three, I captured any Exceptions or OLEDB events via SQL Profiler:

Events:

Errors and Warnings

Attention
Exception
Execution Warnings
User Error Message

OLEDB

all

TSQL

all except:

SQL:StmtRecompile
XQuery Static Type

Column Filters:

ApplicationName

NOT LIKE %Intellisense%

SPID

Greater than or equal 50

THE TESTS

Test 1

CAST(NULL AS UNIQUEIDENTIFIER) that works

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
             , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

Relevant portion of the XML execution plan:

          <DefinedValue>
            <ColumnReference Column="Expr1002" />
            <ScalarOperator ScalarString="NULL">
              <Const ConstValue="NULL" />
            </ScalarOperator>
          </DefinedValue>
  ...
<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT 1 FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
 />

Test 2

CAST(NULL AS UNIQUEIDENTIFIER) that fails

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
         --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

(note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)
Test 3

CAST(NULL AS DATE) that works

SELECT TOP (2) CAST(NULL AS DATE) AS [Something]
         --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

(note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)

Relevant portion of the XML execution plan:

          <DefinedValue>
            <ColumnReference Column="Expr1002" />
            <ScalarOperator ScalarString="[Expr1002]">
              <Identifier>
                <ColumnReference Column="Expr1002" />
              </Identifier>
            </ScalarOperator>
          </DefinedValue>
 ...
<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT TOP (2) NULL &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
 />

If you look at Test #3, it is doing a SELECT TOP (2) NULL on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is DATE, as expected. The conversion from INT to DATE, something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is NULL, so it is returned directly, hence the <ColumnReference Column="Expr1002" />.

If you look at Test #1, it is doing a SELECT 1 on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is GUID, as expected. The conversion from INT to GUID (remember, this is done within the driver, and OLEDB calls it "GUID"), something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is not NULL, so it is replaced with a literal NULL, hence the <Const ConstValue="NULL" />.

Test #2 fails, so there is no execution plan. However, it does query the "remote" system successfully, but just can't pass back the result set. The query that SQL Profiler captured is:

SELECT TOP (2) NULL "Expr1002" FROM "TEMPTEST"."sys"."objects" "Tbl1001"

That is that exact same query that is being done in Test #1, yet here it is failing. There are other minor differences, but I cannot fully interpret the OLEDB communication. However, the remote field is still showing as INT (wType = 3 = adInteger / four-byte signed integer / DBTYPE_I4) while the "client" field is still showing as GUID (wType = 72 = adGUID / globally unique identifier / DBTYPE_GUID). The OLE DB documentation doesn't help much as GUID Data Type Conversions, DBDATE Data Type Conversions, and I4 Data Type Conversions show that converting from I4 to either GUID or DBDATE is unsupported, yet the DATE query works.

The Trace XML files for the three tests are located on PasteBin. If you want to see the details of where each test differs from the others, you can save them locally and then do a "diff" on them. The files are:

NullGuidSuccess.xml
NullGuidError.xml
NullDateSuccess.xml

ERGO?

What to do about it? Probably just the work-around I noted in the top section, given that the SQL Native Client -- SQLNCLI11 -- is deprecated as of SQL Server 2012. Most of the MSDN pages on the topic of SQL Server Native Client have the following notice at the top:

  Warning

  SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

For more info, please see:

SQL Server Native Client
Installing SQL Server Native Client

ODBC ??

I set up an ODBC Linked Server via:

EXEC master.dbo.sp_addlinkedserver
  @server = N'LocalODBC',
  @srvproduct=N'{my_server_name}',
  @provider=N'MSDASQL',
  @provstr=N'Driver={SQL Server};Server=(local);Trusted_Connection=Yes;';

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname=N'LocalODBC',
  @useself=N'True',
  @locallogin=NULL,
  @rmtuser=NULL,
  @rmtpassword=NULL;

And then tried:

SELECT CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
FROM [LocalODBC].[tempdb].[sys].[objects] rmt;

and received the following error:

  OLE DB provider "MSDASQL" for linked server "LocalODBC" returned message "Requested conversion is not supported.".
  Msg 7341, Level 16, State 2, Line 53
  Cannot get the current row value of column "(user generated expression).Expr1002" from OLE DB provider "MSDASQL" for linked server "LocalODBC".

P.S.

As it relates to transporting GUIDs between remote and local servers, non-NULL values are handled via a special syntax. I noticed the following OLE DB Event info in the SQL Profiler trace when I ran CAST(0x00 AS UNIQUEIDENTIFIER):

<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT {guid'00000000-0000-0000-0000-000000000000'} &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
 />

P.P.S.

I also tested via OPENQUERY with the following query:

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
     --, (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM   OPENQUERY([Local], N'SELECT 705 AS [dummy] FROM [TEMPTEST].[sys].[objects];') rmt;

and it succeeded, even without the local object reference. The SQL Profiler trace XML file has been posted to PasteBin at:

NullGuidSuccessOPENQUERY.xml

The XML execution plan shows it using a NULL constant, same as in Test #1.


How to login to Sybase ASE using OS credentials? I come from an Oracle background, and I'm used to the concept of OS users that can login to Oracle with DBA privileges in the machine the DB is installed on using their OS credentials as long as the OS user is part of a certain OS group (oinstall) using the below syntax:

sqlplus / as sysdba

In a machine (Oracle Linux 6.3) where Sybase ASE (15.7) is installed, is there any way I can login the same way as sa user?!

I'm assuming sqlplus is designed to allow password-less DB access under certain scenarios (eg, a custom perl script running under the same scenario doesn't, by default, have password-less access to the Oracle DB) ... ?

SAP(Sybase) ASE does not come with any applications which, by default, allow for password-less access to the dataserver.

Having said that ... it is possible to setup password-less access, eg:

design your own custom app that provides password-less access [will likely have to rely on a password file, or an approved method of external authentication]
configure your environment with one of the approved methods of external authentication [see ASE Security Admin, External Authentication for more details]


postgresql out-of-memory I am running postgresql 9.2 on a 32-bit machine running windows xp with 4 gb of memory and I am getting out of memory errors.  I suspect it has something to do with these two parameters I have changed related to memory:

shared_buffers = 1024 MB
maintenance_work_mem = 1024 MB

Before changing these parameters, I was not getting out-of-memory errors.  Is this too much for a 32-bit machine?

I usually prefer to start with lower shared buffers on both Linux and Windows, and keep work mem low also.  Maintenance_work_mem can be left generally the default too.  Keep in mind that too low a maintenance_work_mem may slow down db restores but it won't stop it.  Generally you are better off starting low and then tweaking upward to find sweet spots than starting too high and working your way down.

Secondly you are reserving half of the memory on your box for PostgreSQL and only PostgreSQL.  This can have very unfortunate side effects if you are doing anything else.  Since this is Windows XP I am assuming it is not a dedicated server, and you are going to do far better with conservative settings here.


Mysql - Average price for top 10% cheapest volume (algorithm help) My question is very similar to this but not the same. The table is the same: rows with ID, volume, and price. In short, I need an SQL query that will give me the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows. In even shorter terms, average price for the 10% cheapest volume. For a better explanation, I've written an algorithm in pseudocode on how I would have done it in C++:

Sort table by price (lowest first).
Calculate total volume and store 10% of that value in a variable named 'threshold'.
Declare a variable 'sum' with value 0 and start a loop that will iterate through the aforementioned sorted table.
Check if 'sum' is less than 'threshold'. If true, add the row's volume value to 'sum', and add the examined row to a list. Increase iterator by 1.
Repeat step 4 until condition is false. When false, it should break the loop and return the list.
Make an arithmetic average of the returned row's prices.

I've written the entire thing with three explanations as to make sure people understand and hopefully provide their own insight on how to solve this in SQL. There might be a single function, or a very simple query that can solve this entire thing, so I gave a detailed context to the problem.

If you are using a older MySQL versions like 5.1 - 5.7 , you need to use MySQL user variables as these MySQL versions does not support window functions and or common table expressions ..

I ported the answers of @Akina and @mustaccio and the  fiddle of Akina
to a MySQL 5.1+ working query.

Warning as MySQL 5.1 to MySQL 5.7 does not use window functions and or common table expressions  you have to write tricky MySQL code like this.  Note if you are using MySQL 8 use one off those answers.

Query

SELECT
 AVG(price)
FROM (

  SELECT
     t.price
   , (@RUNNING_SUM := @RUNNING_SUM + t.volume) AS running_sum
  FROM
   t
  CROSS JOIN (SELECT @RUNNING_SUM := 0) AS init_user_param
  ORDER BY
   t.price ASC
) AS alias
WHERE
 alias.running_sum <= (0.1 * (
                         SELECT
                            MAX(running_sum)
                         FROM (
                           SELECT
                                t.price
                             , (@RUNNING_SUM_1 := @RUNNING_SUM_1 + t.volume) AS running_sum
                            FROM
                           t
                           CROSS JOIN (SELECT @RUNNING_SUM_1 := 0) AS init_user_param
                         ORDER BY
                         t.price ASC
                      ) AS a
                ))

see demo


How do you tune MySQL for a heavy InnoDB workload? Assuming a production OLTP system with predominantly InnoDB tables

What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from their defaults?
How do you spot potential bottlenecks before there is a problem?
How do you recognize and troubleshoot active problems?

Any anecdotes detailing specific status variables and diagnostics would be appreciated.

Interestingly, in MySQL 5.5, you can now have multiple innodb buffer pools.
The parameters you care about are

innodb_buffer_pool_instances (default is 1)
innodb_buffer_pool_size (default is 1G)

In about a month, I am slated to implement 112 innodb buffer pools for a client. I'll let you know how it went.
UPDATE 2011-02-27 21:57 EDT
I found out the max value for innodb_buffer_pool_instances is 64
I decided to configure 144 GB , so I set innodb_buffer_pool_instances to 18 and innodb_buffer_pool_size to 8. I am currently loading the server with 450GB
UPDATE 2011-04-28 13:44 EDT
I tried out multiple InnoDB Buffer Pools. There was too much threading locking and contention. I changed over to a single 162GB Buffer Pool + setting read_io_threads and write_io_threads to 64 (maximum value). This worked way better.
UPDATE 2012-07-03 17:27 EDT
I learned something amazing about MySQL. If you allocate a single monolithic InnoDB Buffer Pool that is bigger that Total Installed Divided By Number of Physical CPUs, your will incite the OS to regular intervals memory swapping due to a full InnoDB Buffer Pool. MySQL 5.5's option known as innodb_buffer_pool_instances can be used to split up the buffer pool. Yesterday, I properly implemented this for the client I mentioned in my answer last year. I still have 162GB for the client's Buffer Pool. I have set the server's innodb_buffer_pool_instances option to 2 because each DB Server is dual hexacore. I was thinking of setting it to 12 but then a colleague showed me a blog from Jeremy Cole on MySQL and Swappiness. After reading it, I put it into practice immediately for my client. I ran this command
numactl --hardware

I saw a mapping of 192GB of server RAM as 96GB to each physical core. Therefore, I set the innodb_buffer_pool_instances to 2. Things are looking good right now. I will update my answer to see how this affects memory swapping for the next 2 montns.


AFTER UPDATE trigger that modifies the table on which it is defined On MySQL 8.0  Trigger created:

DROP TRIGGER `Metrosky_spotters_2020_au`;
DELIMITER ;;
CREATE TRIGGER `Metrosky_spotters_2020_au` AFTER UPDATE ON `Metrosky_spotters_2020` FOR EACH ROW
UPDATE Metrosky_spotters_2020 SET NEW.trnmswid = 5000;;
DELIMITER ;

The trigger  loads okay.
The function to edit a row but in a change to a field in that row- then t=fails the trigger functionality with:

  Can't update table 'Metrosky_spotters_2020' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The idea here is to add the current users MSWID (or login) (using 5000 as test)  to  trnmswid whenever a row item is updated.

I've also tried this in BEFORE UPDATE  and still fails.

Is SET command incorrect or what am I missing?

You don't need, neither you can have (as the error message suggests), the UPDATE statement against the table on which the trigger fires. You can, however, directly set the value any NEW column in a BEFORE trigger:

CREATE TRIGGER `Metrosky_spotters_2020_au`
BEFORE UPDATE ON `Metrosky_spotters_2020` FOR EACH ROW
SET NEW.trnmswid = 5000


'Connection not found' error thrown at end of task, after the package has successfully used the connection it refers to as missing Sorry for the long title and this long question!

I'll set the scene. I'm fairly new to VS and SSIS.

I have created several SSIS packages, basically a package for retrieving data and creating a csv file, getCSV.dtsx, and another to Load said csv file into our Oracle tables, loadData.dtsx.

Now that they are working independently, I have started to put them together in one 'main' package getandLoad_AllTables.dtsx consisting of a sequence container for each table, 2 packages per container; getData/loadData using 'Execute Package Tasks' to call these packages from the local file system in the order I have laid out in my control flow.

The issue - both the tasks are called, run and completed within the first container however the getandLoad_AllTables package errors out, detailing that

Error: 0xC001000E at loadData: The connection "{long-ass string}" is not found"

I checked the ID of the connection in the XML of loadData.dtsx and found it is the main connection used within all the 'Execute SQL Tasks' in the loadData package, all of which ran successfully and loaded the data into the tables as expected.

Why is this error thrown in the getandLoad_AllTables package and not in the loadData package it says the connection is missing from? I am completely stumped.

Thank you in advance for any help/guidance/advice anyone can give.

I figured this out and documenting here in case anyone comes across this question with the same issue.

Within the Execute Package Task Editor I had chosen the Reference Type as External Reference and was linking to the .dtsx file within the file system. It seems that this option, when run directly from Visual Studio at least, wasn't cleaning up properly.

To fix, I changed the Reference Type to Project Reference and choose the required package from the list of package within the project. This ran 100%.

I have gone as far as setting this up on management studio, but to run from within Visual Studio, this fixed the issue.


How can I optimise an Update on a large table? I am using Oracle 11g, I have to update 7 fields on a table with 14 millions of rows, the updated columns get the new value from functions, the update looks like:

update hslak
set HSLAKSTS =  reverseheb(hslaksts) ,
HSLAKSBSTS=reversehebchars(hslaksbsts),
HSLAKIVTK=reverseheb(HSLAKIVTK),
HSLAKMKOR=reverseheb(HSLAKMKOR),
HSLAKIRGTG=reverseheb(HSLAKIRGTG),
HSLAKMCR= reverseheb(HSLAKMCR),
HSLAKMASL=reverseheb(HSLAKMASL);

it run for 6-7 hours and it is not over yet.

Would it be faster to create a new table rather then update it? Would it be better to create a loop and update 100000 records each time and commit after each iteration?

Is the process I/O bound (there is lots of disk activity) or CPU bound (which is possible if the functions are complex)? It is most likely the former and if so then if there are indexes covering those columns it is usually better to drop them before operations that update every row and recreate them afterwards (don't drop the PK and/or clustered index though). "Usually" is an important word here: check it out in a test environment to confirm for your data.

  Would it be faster to create a new table rather then update it?

Probably not, unless the new table is on physically separate media so reading from the old and writing to the new does not cause head-thrashing on the physical disks.

  Would it be better to create a loop and update 100000 records each time and commit after each iteration?

If you and your users can cope with the data being partially updated at any given time then this (probably with a smaller number of rows then 100,000) gives you the advantage of not blocking your application for other uses as you don't have an update lock on the entire table for the length of time it takes to update all the rows. I've not used postgres much but assuming it behaves similarly to MS SQL Server (in simple recovery mode) using smaller updates inside explicit transactions can greatly reduce the amount of log file growth but is unlikely to significantly affect overall run-time.

If running the update in batches and allowing normal operations to continue on the application then obviously you shouldn't follow the first recommendation to drop indexes at the start & recreate them at the end - those indexes are likely to be essential to reasonable performance of the application.


Ignoring temp table in Postgres event trigger I am trying to have a trigger that gets invoked when new tables, except temporary tables, are created.
This is what I have tried:
CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
    END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_event ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE FUNCTION', 'ALTER TABLE', 'DROP TABLE')
 EXECUTE PROCEDURE insert();

create TEMP table my_table(id serial primary key);

This is the output I see:
CREATE TABLE
CREATE FUNCTION
CREATE EVENT TRIGGER
CREATE FUNCTION
CREATE EVENT TRIGGER
NOTICE:  caught CREATE SEQUENCE event on pg_temp.my_table_id_seq
NOTICE:  caught CREATE TABLE event on pg_temp.my_table
NOTICE:  caught CREATE INDEX event on pg_temp.my_table_pkey
NOTICE:  caught ALTER SEQUENCE event on pg_temp.my_table_id_seq

How do I exclude temporary tables from invoking the trigger?

You'll have to exclude temporary objects by schema name:
FOR r IN
   SELECT * FROM pg_event_trigger_ddl_commands() AS ddl
   WHERE ddl.schema_name NOT LIKE 'pg_temp%'
LOOP
   ...
END LOOP;

The strange pattern is because temporary objects in PostgreSQL are created in schemas that are actually called pg_temp_1, pg_temp_2 and the like (a different one per user), and even though your example suggests that you always see pg_temp, I'd prefer to use the pattern just to be on the safe side.


Output parameter not set if stored procedure fails inside a TRY / CATCH In SQL Server 2008 (but also in 2014). Let's consider a procedure that has an output parameter. This procedure may produce an error (and will in the following example). I note that the behaviour of the output parameter is not the same if we call the procedure within a TRY / CATCH block.

Example:

create procedure test_output @result varchar(10) output
as
begin
    set @result = 'hello'
    raiserror('This is an error', 16,1)
    set @result = 'error'
end

If we launch the procedure the simple way:

declare @res1 varchar(10)
exec test_output @result = @res1 out
print 'Result is: '+ isnull(@res1, 'empty')

we get (and I'm fine with it):

  Msg 50000, Level 16, State 1, Procedure test_output, Line 7 [Batch Start Line 12]
  This is an error
  Result is: error

If the procedure is now in a try/catch block:

declare @res2 varchar(10)
declare @error_message varchar(max)
begin try
    exec test_output @result = @res2 out
end try
begin catch
    set @error_message = error_message()
    raiserror(@error_message, 16,1)
    print 'Result is: '+ isnull(@res2, 'empty')
end catch

we get (and I'm upset):

  Msg 50000, Level 16, State 1, Line 28
  This is an error
  Result is: empty

The error message is OK, but the output parameter is now NULL. If, in a TRY...CATCH context, the execution is halted immediately after the RAISERROR, I would have expected the output value to be set to hello.

Why is it so?

You're off to a good start with that test setup, but it is missing something that is causing you to misinterpret what is actually happening. If you put in PRINT statements at the beginning, middle, and end of the stored procedure then the additional output will make it clearer as to what is going on here. For example:

USE [tempdb];
GO
CREATE PROCEDURE test_output
(
  @Result VARCHAR(10) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;

    SET @Result = 'hello';

    PRINT 1;
    RAISERROR('This is an error', 16, 1);

    PRINT 2;
    SET @Result = 'error';

    PRINT 3;
END;
GO

The output from your first test query is:

1
Msg 50000, Level 16, State 1, Procedure test_output, Line xxxx [Batch Start Line yyyyy]
This is an error
2
3
Result is: error

And that is probably what you were expecting anyway. But, the output from the second test query is:

1
Msg 50000, Level 16, State 1, Line xxxxx
This is an error
Result is: empty

That is quite a bit different. We can now see that within the TRY...CATCH construct, execution is halted immediately upon the RAISERROR being called (i.e. it becomes a batch-aborting event). On the other hand, RAISERROR does not immediately halt execution when not called within a TRY...CATCH construct.

However, as you pointed out in your update to the question, this does not explain why the OUTPUT parameter is not then set to hello. That is due to the intention of normal stored procedure behavior to not reflect partial execution (due to a batch-aborting error). This is discussed in the following blog post:

TSQL Basics II – Parameter Passing Semantics

Meaning: even though the stored procedure did execute the step setting the variable to hello, the RAISERROR is now a batch-aborting error when called within a TRY...CATCH construct, and stored procedures do not reflect any changes to parameters when they are aborted.

This behavior is also at the heart of the following explanation:

Why must TVPs be READONLY, and why can't parameters of other types be READONLY


Restore a database with a different name on the same server I need a copy of a live database called Carers on the same server for testing purposes. The test database name will be Carers_Test.

I took a backup of Carers using SSMS. And tried to restore it using the GUI. When I followed the steps, the only thing I changed was the name of the database.

But the restore fails with the error:

  Exclusive access could not be obtained because the database is in use.

I do not want to do anything with the current live database, why is that matter that the database is in use?
I have tried to restore the same backup on my local host and it restored successfully. When I try to restore it on the same server with the live database then I get this error.

Please someone help me...I'm going crazy!

It's really simple to solve. You are trying to override the original files you need to you use the move option like this:
restore database <Your-Database> from disk ='<backup-file-path>'
with move 'mdf_file_name' to '<new-path>',
move 'log_file_name' to '<new-path>'

By the way to discover the file name from backup use this command:
restore filelistonly from disk ='<backup-file-path>'


Auditing specific database events For auditing purposes, we have a requirement in which we have to enable Auditing for theses events:

DBA Logins
Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.)

Changes made by our application are stored in tables (CreatedBy, ModifiedBy, CreatedOn, ModifiedOn) and row changes are stored in XML in a specific table so we won't need to log changes made by our application.

Previously we had C2 Audit Mode temporarily enabled, but due to data volume and performance considerations and having these requirements in mind we considered it excessive and disabled it.

Enabling C2 Audit mode is fairly easy, how can i configure the database to perform this kind of logging ?

Additional Notes:

Currently our server uses SQL Server 2008 R2 Enterprise but since we have mostly data storage and reporting services we'll be downgrading to SQL Server 2008 R2 Standard .

SQL Server 2008 (r1) introduced Extended Events and with that came SQL Server Audit and works similar to XEvents to audit events. So it is less intrusive than C2 Auditing and server side traces.

It is very granular and fairly easy to setup. This feature is available in all editions of SQL Server 2008 R2. A good write-up intro/how-to can be found here by Brad McGehee. Thomas LaRock also did a great article on Simple-Talk walking through creating/setting up the audit.

Reading through the articles mentioned above you will note that you can have the events logged to different places (windows app log, file, etc.). From that you can then write PowerShell scripts or T-SQL scripts to alert you of whatever you want. Much more easy than screwing with C2 Audit trace files.


Extended event to track the logins from a specific login, but that login is filtering on a an active directory group I'm wondering if there is a way to do this in sql server. I have an extended event set up to track all user logins from a database with a filter. The way our security is setup, we have active directly groups that we give the access to, and when users login, they are authenticated with their individual active directory account based on belong to the AD group that is granted database permissions.
However, the extended event information only shows that specific user, not the AD group they are authenticating to. For example, if I want to only track logins from DBAs, we have an AD group Company\DBA, and when I login as a member, I authenticate with my Company\User_ID account. However, in the extended event, I only see filters for the specific user, it doesn't display the Company\DBA AD group I'm authenticating with or give me the option to filter from it. Anyone know how to show and filter off of that AD group instead?

You cannot filter on the AD group.
This previous question explains how the access management works with AD groups:
How to tell which windows group login I used when logging in via windows authentication


How do I know if an index has been successfully created in MySQL? I use Capistrano to deploy a Rails app. Capistrano handles running database migrations via essentially a shell script that runs from my local computer.

About 24 hours ago I started a migration that added a compound index to my table (on 3 columns, to be specific, and 9 million records). I returned to my office this morning and the script still hadn't completed.

However, at this point, I have no idea if it's just that my local connection to the database timed out, even though it continued processing the index.

My question is, how do I know if the indexing process has completed?

When I view the table via a MySQL client (Sequel Pro), I do see the indexes listed (with cardinality equal to the correct number of rows). But I'm not sure if these would be listed anyway as part of the initial creation of the indexes.

Welcome.

If you say that the script did not finish / exit...

One crude method to find if the indexing process is in the middle...

You shall try listing the folder contents of /var/lib/mysql/dbname/ and checking the date and time of update of the table names concerned. If the date/time does not change for a while you shall be sure that the process is not touching that table file for as much time. Hence cornering if there is a process snap due to timeout or other.


Does setting a database to single_user prevent delivery of Service Broker messages? Is it safe to assume that while a database is in single_user mode, the queues are not available for message delivery? My understanding is that the special tables that the queues represent reside in the database context, but it's not clear if message delivery counts as a 'connection' in the usual sense.

SINGLE_USER means only a single session can connect to the database, whether that session is SQL Server itself, SQL Server Agent, or some user makes no difference.

If you need to ensure no one can access the database, the only option is to take it offline.

If you are comfortable with members of the sysadmin or dbcreator roles, or the db_owner fixed database role, having access, you can put the database into RESTRICTED_USER mode.  See this MSDN page for details about that.


Partitioning big tables I have a DB with multiple large tables. For example one single table has 738,829,922 rows.
The primary key of the table is a datetime. For better performance and maintainability the table is partitioned by monthly range.
Here is an example of the partition stats:

Around 95% of all query are hitting only the partition of the current month (also only the last 3 days).
Over the month I notice a performance drop the seems to be related to the row count of the havely use partition. It instancly goes back to normal after switching to a new partition.

I worked hard with the microsoft support to optimize all to the maximum. Thinks like statistics, fragmentation, parameter sniffing, query plans are well handled and leave not much room for optimization.
Question
I hope smaller partitions (weekly or daily) might solve the problem. To try this I have a gerneral questions abount partitions.
Are there some negative effects of having around 3,000 partitions on a single table? Are there other tips/tricks about partitioning table of this size?

General answer is that you could remove the paritioning and the performance would not drop. Hitting only one partition is quite similar in performance compared to filtering out 95% of the row based on a proper index. Partitiions generally are not for increasing select performance, but for allowing super fast DROPPING of partitions (when the data falls out of scope).

So, you really need to do the basics - get the slow queries, analyze them, find out how to write them better and / or whether other queries are blocking them. Unless you have really special cases (which we can not evaluate given the information you give)... the partition is totally not helping you in the selects.


Is there a way to include an assertion about the expected number of rows? I have a function that abstracts updating event data:

async (
  connection: DatabaseConnectionType,
  id: number,
  venueId: number,
  movieId: number,
  cinemaMovieNameId: number,
  date: string,
  time: string,
  url: string | null,
  seatingIsAllocated: boolean | null,
  reservationFee: number | null,
  reservationPerTicketFee: number | null,
  guide: $PropertyType<EventResultType, 'guide'> | null
): Promise<number> => {
  const timezone = await connection.maybeOneFirst(sql`
    SELECT
      locality.timezone
    FROM venue
    INNER JOIN locality
    ON locality.id = venue.locality_id
    WHERE venue.id = ${venueId}
  `);

  if (!timezone) {
    log.error({
      venueId
    }, 'timezone not found');

    throw new Error('Timezone not found.');
  }

  // @todo Prevent unncessary updates. See updateVenue and updateTicket.
  await connection
    .query(sql`
      UPDATE
        event
      SET
        venue_id = ${venueId},
        movie_id = ${movieId},
        cinema_movie_name_id = ${cinemaMovieNameId},
        starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE (${timezone}),
        url = ${url},
        seating_is_allocated = ${seatingIsAllocated},
        reservation_fee = ${reservationFee},
        reservation_per_ticket_fee = ${reservationPerTicketFee},
        guide = ${guide ? JSON.stringify(guide) : null}
      WHERE
        id = ${id}
    `);

  return id;
};

It seems redundant separate locality.timezone lookup query from the UPDATE query. However, combining these two queries requires asserting within a query that timezone exists.

Is there a syntax to assert how many results a query returns?

Something that would allow me to rewrite the above into ~:

await connection
  .query(sql`
    WITH timezone AS (
      SELECT
        locality.timezone
      FROM venue
      INNER JOIN locality
      ON locality.id = venue.locality_id
      WHERE venue.id = ${venueId}
      -- Made up syntax
      ASSERT ROW COUNT 1
    )
    UPDATE
      event
    SET
      venue_id = ${venueId},
      movie_id = ${movieId},
      cinema_movie_name_id = ${cinemaMovieNameId},
      starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE ((SELECT timezone FROM timezone)),
      url = ${url},
      seating_is_allocated = ${seatingIsAllocated},
      reservation_fee = ${reservationFee},
      reservation_per_ticket_fee = ${reservationPerTicketFee},
      guide = ${guide ? JSON.stringify(guide) : null}
    WHERE
      id = ${id}
  `);

It is not an assert, but you can just have the query return the empty string (rather than NULL) when there is no match:

... AT TIME ZONE ((SELECT timezone FROM timezone union select '' limit 1))

when invoked with empty timezone query gives:

ERROR:  time zone "" not recognized


JOIN with the last id I want to select a record from one table (customers table) and join only with the last from other related table (sales table). I know two ways of doing it, but I don't know which one have better performance. What is the best approach?

Note: in my case I'll get only one row because I want the data for one specific customer but what if I want all the main rows, does it change something?

Option 1, using sub-query in WHERE:

SELECT c.id, c.name, s.id, s.cost
FROM
  customers as c
  LEFT JOIN sales as s ON s.customer_id = c.id
WHERE
  c.id = 42
  AND s.id = (SELECT MAX(s2.id) FROM sales as s2 WHERE s2.customer_id = c.id) ;

Option 2, using sub-query in FROM:

SELECT c.id, c.name, s.id, s.cost
FROM
  customers as c
  LEFT JOIN (
    SELECT s2.customer_id as cid, MAX(s2.id) as maxid
    FROM sales as s2
    WHERE s2.customer_id = 42
    GROUP BY s2.customer_id
  ) maxids ON c.id = maxids.cid
  LEFT JOIN sales as s ON s.id = maxids.maxid
WHERE
  c.id = 42 ;

Here are the tables CREATE:

CREATE TABLE `customers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(120) COLLATE utf8_spanish_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22149 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_c

CREATE TABLE `sales` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) DEFAULT NULL,
 `status` tinyint(3) unsigned DEFAULT NULL,
 `buydate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `cost` decimal(10,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `customer_key` (`customer_id`) USING BTREE,
 KEY `status_key` (`status`) USING BTREE,
 CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11555 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

And here their EXPLAINS:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY c   NULL    const   PRIMARY PRIMARY 4   const   1   100.00  NULL
1   PRIMARY s   NULL    const   PRIMARY,customer_key    PRIMARY 4   const   1   100.00  NULL
2   DEPENDENT SUBQUERY  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY c   NULL    const   PRIMARY PRIMARY 4   const   1   100.00  NULL
1   PRIMARY <derived2>  NULL    system  NULL    NULL    NULL    NULL    1   100.00  NULL
1   PRIMARY s   NULL    const   PRIMARY PRIMARY 4   const   1   100.00  NULL
DERIVED s2  NULL    ref customer_key    customer_key    5   const   11  100.00

I sometimes find this technique useful for judging the performance of a query, or for comparing two possible queries.  It is especially helpful before the table is large enough to get reliable timings.

FLUSH STATUS;
SELECT ... ;
SHOW SESSION STATUS LIKE 'Handler%';

That will give a few counters.  If I see numbers that are about equal to the number of rows in a table I am selecting from, then I know there was a table scan.  Numbers near the resultset size tend to be more beneficial.  Handler...write... indicates that a temp table needed to be generated, and the number says how big it was.

Even without understanding what the numbers represent, it is often 'obvious' which of two SELECTs is better.


SQL Server - Move data from secondary ndf file to primary mdf file SQL Server 2016 - We have two files - one mdf on Primary and one ndf on Secondary filegroup.

We want to move data from secondary to primary and then delete secondary:

Approach 1:

Create clustered index on table which are on secondary and setting location of index to primary (Question: is clustered index only option or non clustered index will work too)

Approach 2:

DBCC SHRINKFILE (nameofdatafile, EMPTYFILE);
GO

-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2012
REMOVE FILE Test1data;
GO

But files are in different file group, will above works

Any suggestions

If the datafile are in different filegroup the approach number 2 won't work, it will only work on the same filegroup.

For the Approach 1, please check: https://stackoverflow.com/questions/2438019/how-i-can-move-table-to-another-filegroup/31351806

I wouldn't recommend to move data back to primary filegroup, this should be only used for system objects and should be kept small for in case where you need to do piece meal recovery.


Table size and row count My database is in TB with different schemas. I would like to search in each schema following information:

Schema name
Table name
Table size
Rows count
Table Created date

Where table name contains: *.bak or dropped_* or *_drop

The tables appear as entries in pg_catalog.pg_class  that have relkind='r'
(see the documentation).

table name: pg_class.relname

schema name: pg_namespace.nspname. Join pg_class.relnamespace to pg_namespace.oid to follow the association between the table and its schema.

table size: see pg_relation_size(pg_class.oid)

row count: pg_class.reltuples gives an estimate. For an exact number, run select count(*) from the table.

created date: the creation date is not recorded by postgres.

Some of these are also available through views in information_schema if you prefer. The structures of the views in this schema are dictated by the SQL standard, whereas the pg_catalog tables are postgres-specific.
Example of query
Starting from the query provided as a comment:
  select nspname as schema, relname as tablename,
  reltuples as rowcounts
  from pg_class c JOIN pg_catalog.pg_namespace n
   ON n.oid = c.relnamespace where relkind='r'
   and relname like '%_drop%'
   order by nspname, reltuples desc

remarks:

The underscore character is a wildcard for the LIKE operator (it matches any character), so it needs to be quoted to be interpreted literally. By default, the quote character is backslash.

pg_relation_size() as a function call can be introduced directly in the select list.

Amended query:
  select
    nspname as schema,
    relname as tablename,
    reltuples as rowcounts,
    pg_relation_size(c.oid) AS tablesize
  from pg_class c JOIN pg_catalog.pg_namespace n
   ON n.oid = c.relnamespace where relkind='r'
   and relname like '%\_drop%'
   order by nspname, reltuples desc


Is there anyway to view historical performance of SQL 2008 R2 database? So.. I was wondering, is there a way for me to generate any report for the historical performance of a MS SQL database? I'm originally an Oracle DBA, but now I'm requested to support a few SQL databases in my office as there's nobody supporting it now.
Being an Oracle DBA, I know where to look if I need any reports related to DB performance e.g. AWR & Statspack but now MS SQL DB.
Going around google doesn't yield any result as well unfortunately, since most requested me to turn on SQL profiler to run the trace, which I can't since it will cause even more load to the high loaded server.
Is there a way to configure the DB to take snapshots every half hour and combine it into a report like how AWR / Statspack behave in Oracle?

System Dynamic Management Views

You might want to dive into the Execution Related Dynamic Management Views and Functions (Transact-SQL) that reside in a SQL Server instance. These views and functions do contains some information related to performance.

The suggested views and functions are a sub-set of the System Dynamic Management Views which contain various other sub-sets of views and functions, which may or may not be what you are looking for.

Further sub-sets

Database Related Dynamic Management Views (Transact-SQL)
Index Related Dynamic Management Views and Functions (Transact-SQL)
I O Related Dynamic Management Views and Functions (Transact-SQL)
Memory-Optimized Table Dynamic Management Views (Transact-SQL)
Transaction Related Dynamic Management Views and Functions (Transact-SQL)
...

(Just to name a few)

  Note
  Most of these views  are recycled after an instance restart. You will have to dump the information to static tables in a (e.g.) Admin database if you want to retain historical data.

Built-In Reports

If you right-click on a database and move your mouse pointer into the reports, then you might find can occasional jewel of information:

Creating a Baseline for SQL Server

There is an extensive Q&A on DBA.SE with the title Creating SQL Server Performance Baseline Monitoring which show you how you could baseline a SQL Server instance. The article references other material.

And then you could start reading Erin Stellato's article 5 Reasons You Must Start Capturing Baseline Data for an interesting start into SQL Server "baselining".

Answering Your Question

  So.. I was wondering, is there a way for me to generate any report for the historical performance of a MS SQL database?

There are indeed a couple of ways with the Management Views and Functions and Standard Reports. For a deep dive you are going to either build something yourself or purchase a third-party tool.


Database is too large due to appended backup My database MDF file is at around 600mb. LDF file is at 147MB.

I seriously doubt that it should be anywhere near this.

I suspect it's because the database has been backed up by my host with the "Append to the existing backup set" option checked.

How can I get the database to a reduced size? My host has sent me the .bak file and I've restored it.

Also is there a way to check if the database is actually this big, i.e is in the data causing the large size?

I'm seriously doubting that it should be anywhere near this.

Run sp_spaceused to analyze the actual space in use in the MDF. Run DBCC LOGINFO to analyze the space used in the LDF. Measurement beats guess every single time.

  I suspect it's because the db has been backed up by my host with the
  "Append to the existing backup set" option checked.

Absolutely unrelated.

  how can i get the db to a reduced size?

First, measure it, see what is the required size. If the database is indeed largely empty, first you need to understand why it reached a larger size. Unless you have a clear explanation why it grew (the backup option has, again, absolutely nothing to do with database growth) you cannot device a plan how to reduce the size. As things stand right now, w/o actual root cause analysis, the verdict is that the database has the right size and should not be touched. We cannot recommend any action w/o actually knowing 1) if the database is actually larger than needed, 2) what part of the database is actually too large (reserved space consumed by ghosts, unallocated space, log VLFs etc) and 3) what is the action that causes growth.

Of course, you can always shoot first ask questions later (like any good self respecting DBA from Monument Valley would...) and attempt to shrink the database w/o further analysis, but I hope you're better than that.


How to change pg_largeobject data from psql? (posgresql-9.1) 16-byte password hashes of some application are stored in the postgresql-9.1 database as LOB data. (I don't know why)

So I can get hash:

# select * from pg_largeobject where loid=16916;
 loid  | pageno |                data
-------+--------+------------------------------------
 16916 |      0 | \x098f6bcd4621d373cade4e832627b4f6

How can I replace data of this blob or create new blob with specified data in postgres-9.1?

I see that there is lo_from_bytea function since posgresql-9.4. But there is no something like this in 9.1.

I can use lo_import - load lob from external file, but it is hard-way.
Is there something simplier?

Note that PostgreSQL version 9.1 has been obsoleted in 2016, it should no longer be used.

Anyway, there is a set of large object server-side functions in 9.1 that should cover your needs. They tend to expose a large object as if it was a file, providing file-like primitives.

For instance, here's a plpgsql function that replaces the value inside a large object using these primitives. It should work with 9.1 or newer:

CREATE FUNCTION lo_replace(_loid oid, _str bytea) RETURNS void
AS $$
declare
 fd integer;
begin
 fd = lo_open(_loid, 393216); -- INV_READ|INV_WRITE
 if (fd < 0) then
   raise exception 'Failed to open large object %', _loid;
 end if;

 if (lo_truncate(fd, 0) != 0) then
   raise exception 'Failed to truncate large object %', _loid;
 end if;

 if (lowrite(fd, _str) != octet_length(_str)) then
   raise exception 'Failed to write to large object %', _loid;
 end if;

 if (lo_close(fd) != 0) then
   raise exception 'Failed to close large object %', _loid;
 end if;
end;
$$ LANGUAGE plpgsql;


Is there any reason to have nonclustered indexes on a table with only 1 page in the clustered index? I have a small but actively queried table in SQL Server that has 94 rows that are frequently read and\or updated. The clustered index safely fits into 1 8KB page and has significant empty space on that page. This leads me to believe that any future updates won't push it onto a second page.
In addition to the clustered index, over the years several nonclustered indexes have been added.
My question is, on a conceptual level, can the nonclustered indexes improve SELECT performance? If SQL Server can't read less than 1 data page at a time, then any nonclustered index read could be just as easily satisfied by reading the single page for the clustered index or am I misunderstanding the concept?
I don't wan to include the table definition, but here's some stats from sp_BlitzIndex on that table showing it's use:
ClusteredIndex      Reads: 1 (1 scan) Writes:180,544,146
Nonclustered Index  Reads: 0 Writes:103
Nonclustered Index  Reads: 63,425,182 (57,447,576 seek 5,977,606 scan) Writes:180,544,146
Nonclustered Index  Reads: 150,953,542 (150,953,542 seek) Writes:180,233,055
Nonclustered Index  Reads: 0 Writes:311,091

The clustered index doesn't seem to get read, but it I would think it could without the other indexes. Further interesting detail, sp_BlitzLock shows 39 deadlocks in this database and all 39 involved this tiny table, which I find interesting.

do the nonclustered indexes even matter?

Yes.  You have multiple copies of this table (or subsets of it), and updates have to be coordinated across all the copies.  Query performance is probably not enhanced by the non-clustered indexes, but they are probably the cause of your deadlocks.
But for SELECT query performance, the non-clustered indexes shouldn't help.


Are queries called parallel or in sequence? Given this table:

id | field
----------
 1 |   2

Would it be possible that  the column field is only incremented once, if two processes run in parallel this query:

  UPDATE table SET field = field + 1 WHERE id = 1

In other words, if the above query would be executed twice, would it be possible to end up with the following database?

id | field
----------
 1 |   3

I searched at https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html but I was not able to find an answer there.

With autocommit = ON, the two queries will run one after the other.

With

BEGIN;
some stuff
that UPDATE
more stuff
ROLLBACK; -- either explicitly, or because of some error

the UPDATE will be undone.

Check for errors after every query.

(If this does not suffice, please provide more info.)


Error while upgrading SQL server 2012 to SQL 2017 : Failed to create a new folder 'X:\\xxxx'. The specified path is invalid Failed to create a new folder 'X:\MSSQL\DATA\MSSQL11.MSSQLSERVER\MSSQL\LOG'. The specified path is invalid (for example, it is on an unmapped drive).
I checked there is no X drive present in the server. I verified that all the default files for data and log points to the drives which are available in the server.
Not sure , how do I resolve this as i don't see X drive mentioned in any of the properties.
I have checked in Facets and database properties to verify the path.

At the first sign of trouble during an in-place upgrade, fallback to a side-by-side upgrade.  Install a new instance of SQL 2017, preferably on a new VM, and migrate the databases, logins, jobs and settings from your old instance.


migration sql 2003 to 2008, Sql side and php side I have a system in php using MSSQL 2003. I need to change the database to MMSQL 2008.

I want to know if there's issue I should look at on

SQL Side (Query/SP/....)
PHP Side (Query, connection type, ...)

Answer will be usefull even if it is a "dont worry, using standard connection will work" or "there's not so much depricated function between 2003 and 2008".
If you goth link to other posts answering some of those interrogation or how to see it by myself.

There is no MSSQL 2003, so you must mean MSSQL 2005, right? Are you trying to upgrade? Do you have a development environment you can test in? That's your best course of action. Other than that, I'm not sure what you're asking exactly.

But I've upgraded from 2005 to 2008 using a PHP environment before and nothing broke. There might be some deprecated features in 2008 but as far as I know they haven't been removed from the engine, just deprecated. Most everything should work. But again, try it out in a development environment.


SQL Server: does ddladmin rights contain datareader and datawriter rights to the database? Does db_ddladmin fixed database role include db_datareader and db_datawriter rights, so that you don't have to grant those rights explicitly?

I don't believe so. It should only allow them to run DDL not access tables.

You could make your own role and make it be a member of the db_ddladmin, db_datareader and db_datawriter roles.


mongodb - thread over memory limit What does it mean?

2017-04-28T04:37:53.126+0000 D -        [conn1571] thread over memory limit, cleaning up, current: 67k
2017-04-28T04:37:53.911+0000 D -        [conn1585] thread over memory limit, cleaning up, current: 79k

Should I make something to solve it? If I increas server memory will it help?

This is from source code:

// If many clients are used, the per-thread caches become smaller and chances of
// rebalancing of free space during critical sections increases. In such situations,
// it is better to release memory when it is likely the thread will be blocked for
// a long time.

And when numOpenSessions is over 40 and threadCacheSizeBytes (GetThreadCacheSize()) goes under 0x10000, system frees memory.

So, to "prevent" this, you should have less (concurrent) open sessions OR more (cache) memory. You can define (wiredTiger) bigger (than default) cache size, with current memory. But, yes, it's better give more memory than define bigger cache size, because when mongodb have more memory, cache size is automatically bigger.


DB2 XML indexes and "ORDER BY" clause My table looks like this:

CREATE TABLE "Content" (
    "id" VARCHAR(36) NOT NULL,
    "xml" XML
);

I have an index defined

create index "idx_creation_date" on "Content"("xml") generate keys
    using xmlpattern '/*/dateCreated' as sql timestamp;

This index works fine when using it in comparisons (XMLEXISTS), but now I want to use that index to sort my table according with the date my content was created and select the first 20 items.

Sadly, this didn't work

SELECT "Content".* FROM "Content"
    ORDER BY XMLCAST(XMLQUERY('$xml/*/dateCreated' PASSING "xml" as "xml")
    as TIMESTAMP) DESC FETCH FIRST 20 ROWS ONLY

because XMLQUERY doesn't use indexes. The same goes for

xquery
let $items :=
   for $i in db2-fn:xmlcolumn("Content.xml")
   order by $i/*/dateCreated
   return $i
for $i at $count in subsequence($items, 1, 20)
return $i

because let and return can't use indexes.

Anyone has an idea how I can use the existing (and working) index on dateCreated in a ORDER BY query?

Indexes over XML data (a.k.a. "XML Index") differ from relational indexes in several ways. They are used to identify documents and the position of the nodes within for the indexed pattern. Several nodes within a single XML document may match that pattern. For a relational index each row is indexed, for an XML index there might be zero, one or more matches per document (which is stored as part of a relational row). Some more information can be found in the DB2 documentation on Indexing XML Data.

By SQL Standard the XML data type is kind of a black box. It does not allow comparison and hence sorting. What you are trying to do is sort an XML fragment by a specific node. The fragment is already derived from the stored XML value/document. At the time of the processing there could be no connection between the XML index and the fragment. A good reference on XML indexes and their usage was written by Matthias Nicola.

With that background information we can now focus on your specific question. How would DB2 know that a timestamp/date value is present for each row and the related "Content"? How should the sorting be organized? Because of the index entry/document/row relationship outlined above the sorting in the SQL statement won't work.

For your XQuery I assume that the data is untyped, no schema is used. How would DB2 know that dateCreated is of timestamp and that the index should be used? Have you tried a cast around the dateCreated to let DB2 know that timestamp data is expected?

BTW: You can specify multiple XML indexes for the same pattern, but with different data types.


Write lock during mysqldump even using --lock-tables=false When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this?

As I am using MyISAM, does --single-transaction for InnoDB help?

Also, is Master-Slave architecture the ultimate method to handle backing up online DB?

Thanks!

Your Question : When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this?
You need to understand why other cannot update. Using MyISAM, each table experiences a full table lock with every INSERT, UPDATE, and DELETE. Using --lock-tables will attempt to lock every table, but will be held up of SELECTs are being issued in a heavy-read environment. There are other alternatives.
Your Question : As I am using MyISAM, does --single-transaction for InnoDB help?
InnoDB is an ACID-compliant storage engine and can have data updated and read in transactions. Row-level locking  is normally issued in InnoDB, which allows for other to read and write to tables while they being backed up. MyISAM does not allow this given its full-table locking protocol. Therefore, MyISAM is not assisted by --single-transaction at all. If every table you created was InnoDB, then you could perform an online backup on a running master.
Your Question : Also, is Master-Slave architecture the ultimate method to handle backing up online DB?
Master/Slave does make it easier to backup an online database because you perform the backup using mysqldump on the slave while the Master continues to accept INSERTs, UPDATEs, and DELETEs. In addition, this allows for backups that allow for point-in-time recovery. You would simply perform the following steps on a slave:

STOP SLAVE;
mysqldump
START SLAVE;

If you issue this on the Slave at midnight, the data is frozen in time. That way, when you restore the mysqldump, all the tables are from the same timeframe.
SIDENOTE : You are not obligated to convert all the tables on the Slave to InnoDB.
If all tables were InnoDB, using single-transaction would create point-in-time backups also againsa a master. Since you have MyISAM tables, then Master-Slave is the ideal solution, not necessarily the ultimate. There are still other methods out there, but for the sake of simplicity: GO WITH MASTER-SLAVE !!!


MySQL Query fix and speed up I'm newish to MySQL and a lot of my query is pieced together from this site and trial and error, so any help is appreciated.  If I left out anything, or more detail is needed, I will be happy to give what I can.

My query takes, on average, about 90 secs to run.  It also doesn't return all the correct values.  Even if speed can't be fixed, how do I correct it to return the proper values?

I'm trying to get the most recent set of temperatures for a given location and also an average for the last 24hrs.  A location can have up to 20 thermometers.

SELECT t.Common_name, f.Food_name, e.Temperature, e.Temp_timestamp AS Time, Daily_avg.avg_temp
FROM Thermometer t
JOIN Temp_records e
  ON e.Temp_therm_id = t.Therm_id
LEFT JOIN Food f
  ON f.Food_therm_id = t.Therm_id
JOIN (
  SELECT Temp_therm_id, AVG( Temperature ) AS avg_temp
  FROM Temp_records
  WHERE Temp_timestamp >= NOW( ) - INTERVAL 1 DAY
  GROUP BY Temp_therm_id
) AS Daily_avg
USING ( Temp_therm_id )
WHERE t.Therm_unit_id =2
AND Temp_records_id
IN (
  SELECT MAX( Temp_records_id )
  FROM Temp_records
  GROUP BY Temp_therm_id
)

Thermometer Table

Therm_id
Therm_unit_id
Common_name

Temp_records Table (~18k records at the moment)

Temp_records_id
Temp_therm_id
Temp_food_id
Temperature

Food Table

Food_id
Food_unit_id
Food_name
Food_therm_id

What I'm currently seeing:

(The probe isn't in food, that's just the ambient temperature in C)
The db has slightly different temperatures for the given food items, and since I can only get the temperature of one food item at a time, they obviously can't have the same time stamp.

Should I remove the averaging part in to it's own query?

If I remove the food part of the query, it returns my 3 most recent temperatures and their averages. (I only have 3 thermometers for testing at the moment)

Start by changing IN ( SELECT max... ) to = ( SELECT max... ).

Change f.Food_name to

( SELECT Food_name FROM Food WHERE Food_therm_id = t.Therm_id ) AS Food_name

and get rid of the LEFT JOIN Food ... (I am unsure whether the LEFT JOIN before JOIN is causing trouble.)

If those suggestions do not speed it up enough, then...

Please provide EXPLAIN SELECT ... so we can see how the query is executed.

What table is Temp_records_id in?  Perhaps e?  (Please qualify all column names.)


MySQL 5.1 High Cpu usage on CentOS 6 I am running MySQL 5.1 on a CentOS 6 server with 16G ram. I got also apache on it.
MySQL is constantly using too much cpu.
top - 17:40:36 up 112 days, 12:13,  1 user,  load average: 1.24, 1.72, 1.82
Tasks: 222 total,   1 running, 221 sleeping,   0 stopped,   0 zombie
Cpu(s): 12.3%us, 11.6%sy,  0.4%ni, 72.9%id,  2.8%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  16329040k total, 12946136k used,  3382904k free,   587252k buffers
Swap:  1051832k total,  1050988k used,      844k free,  4986272k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25368 mysql     20   0 8854m 5.3g 5064 S 295.4 33.9  11912:37 mysqld
 1757 apache    20   0  982m  80m  28m S  2.0  0.5   0:03.04 httpd
 2494 apache    20   0  878m  68m  27m S  2.0  0.4   0:00.92 httpd

my.cnf
[client]
default-character-set=utf8

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
bind-address=127.0.0.1

key_buffer        = 800M
max_allowed_packet    = 32M
thread_stack        = 256K
thread_cache        = 8
thread_cache_size    = 20
thread_concurrency = 4
max_connections        = 510
table_cache            = 9000
interactive_timeout = 80
tmp_table_size = 540M
max_heap_table_size = 540M
max_connect_errors = 150
wait_timeout = 20
read_rnd_buffer_size = 1024K
connect_timeout = 10
query_cache_type = 1
query_cache_limit   = 40M
query_cache_size        = 128M
innodb_buffer_pool_size = 256M

join_buffer_size = 14M
sort_buffer_size = 14M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

SHOW FULL PROCESSLIST; is returning me on average 2-5 queries.
Here is the output of SHOW VARIABLES; command.
We got MyISAM and InnoDB tables in this system.
Thank you for any suggestions.
EDIT 1
Here is the vmstat output.
EDIT 2
SHOW GLOBAL STATUS output is available below in the comments.

Let's see the slowest query in the system.  Quite likely, fixing it (adding an index, reformulating the SELECT, etc) is the best solution.  You mentioned SHOW FULL PROCESSLIST -- That is likely to show the naughty queries; let's see them, plus the associated SHOW CREATE TABLEs.

query_cache_size        = 128M -- too big; no more than 50M.  A big value slows down the system with pruning.

innodb_buffer_pool_size = 256M and key_buffer        = 800M -- What ENGINE are you using?  Neither of these values is likely to be 'good'.  See http://mysql.rjweb.org/doc.php/memory for further advice.  A poorly-sized cache could lead to extra I/O, which would be seen as slow queries.

table_cache            = 9000 -- Yikes!  How many tables do you have?  (Thousands of tables is an OS problem.)


How do I handle importing high amounts of transaction data into application DB? For the moment, this is a hypothetical question. I've got a database that has a high level of transactional data coming into it. I also have a database that supports my website (my application database) to handle my shopping cart, orders, user accounts etc.

Various pieces of information that are reported on the site, for example stock levels, invoice data, item ordering statuses, new catalog items and a host of other pertinent information needs to be fed to the application and displayed on an ongoing basis, a fairly large amount of data.

Right now, the data is coming into the application database by SSIS and my DBA is telling me that because of the large number of SSIS packages required, it's infeasible to engineer a database that is modeled properly for our client facing applications, which is causing all sorts of problems for our application developers... I'm currently calling B.S. on this, but I'm putting it out there to you experts to give me some advice on best practices for handling input of high amounts of data from the back-end and re-indexing to maintain performance of the website including high requirement features such as catalog search, ordering and user account maintenance.

My initial thoughts are to feed data into the back end system using SSIS into transitional tables which can be loaded and re-indexed without affecting the 'live' tables, and then switch the transitional tables for their live counterparts. Is this feasible? Does it make sense? Is there a better practice? I'm not a DBA so it would be helpful to get some expert advice to take back to the team.

We have a good sized Enterprise system that has many of SSIS packages taking data in and out of the system daily.

Some of the strategies we have used are:

Only process deltas especially in large files. To figure which records are deltas we use change tracking and send that data to tables in a separate database, so the process of figuring out the deltas when we receive a full file is pushed off onto a staging server. Only records that are new or changed go to the real production server.  On our busiest server, we have moved all teh processing except the final load off to a completely separate server.

If the file goes directly to table that does not also have transactional data changes or multiple data sources, we might have two tables (and A and B version) and a view that selects from the active table. So we do our processing on the inactive version (possibly including dropping and recreating indexes), switch the table in the view (meaning there is about 1 second of down time fromteh user perspective), then update the new inactive table.

SSIS packages can be designed to run very quickly or not so quickly. So we may spend a lot of time performance tuning one that takes too long and affects production.

Run the packages during the time period when there is the least usage on the server.


Two Databases in Standby/ Read-Only, Can Only Alter to SINGLE_USER One of Them I am setting up Transaction Log shipping in Standby/ Read-Only mode of two databases to multiple instances.

After backing up and restoring the transaction logs to their separate instances (one copy of each DB on each instance), one of the databases has no issue when I:

ALTER DATABASE [dbname] SET SINGLE USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [dbname] SET MULTI_USER;

the other one throws an error:

Failed to update database "other_dbname" because the database is read-only.

I cannot see any difference between the two. I've checked properties and all of the options are the same. From the options tab, the database that would throw the error had the Compatibility level set to SQL Server 2012 and one that would allow me to ALTER had that value set to SQL Server 2014, but changing them both to 2014 did not resolve the issue.I'm at a complete loss as to what the issue could be or what I could look into that would point me in the correct direction.edit: the above commands are executed on a linked server, if that could have anything to do with it. The users are the same, the permissions are the same, I'm able to execute the commands on the first database with no issue whether they are executed through SSMS or through a job and they always fail on the second database.

options:

Microsoft disclosed an issue in 2015 related to SQL Server 2012 and 2014 that sounds very similar to your problem:

  Assume that you set a Microsoft SQL Server 2012 or SQL Server 2014
  database to read-only mode. When you then try to set it to single-user
  mode, you may receive the following error message:

  "Msg 3906, Level 16, State 1, Line 51 Failed to update database "SDP"
  because the database is read-only.

  Msg 5069, Level 16, State 1, Line 51 ALTER DATABASE statement failed."

You can read about the full issue and the fix in FIX: You cannot set a SQL Server 2012 or 2014 database to single-user mode if the database is read-only.

It looks like you're running SQL Server 2014 SP1 with a security update (from 08/2017) applied. The referenced issue would impact that version of SQL Server, and I believe the fix is in 2014 SP1 CU 2

I'm running SQL Server 2017. In that version, I was able to successfully put a database in single-user then read-only (case-1) and read-only then single-user (case-2):

/**CASE-1**/
--Setting single user then read-only
ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [foo] SET READ_ONLY;

--Setting single user then read-only
ALTER DATABASE [foo] SET READ_WRITE;
ALTER DATABASE [foo] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

/**CASE-2**/
--Setting single user then read-only
ALTER DATABASE [foo] SET READ_ONLY;
ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--Setting single user then read-only
ALTER DATABASE [foo] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [foo] SET READ_WRITE;

I checked the documentation for SQL Server 2014, and it looks like this functionality works the same as in 2017 (at least there is no noted difference). So, I take back my earlier assertion that you can't place a read-only database in single-user mode.


Will autovacuum block DROP or TRUNCATE? When the autovacuum process is vacuuming a large table, will queries like DROP and TRUNCATE be blocked until vacuuming completed?
In the documentation,it says

  Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.)

But I wonder if DROP or TRUNCATE is included.

Autovacuum and VACUUM behave differently. They do both obtain the same lock, but the autovacuum will get cancelled if it is detected to be blocking something else.  DROP or TRUNCATE will be blocked, but in the case of autovacuum it will only for at most 1 second (or whatever "deadlock_timeout" is set to).

An exception to this is an autovacuum "to prevent wraparound".  Those will not get cancelled, which can be a major problem.  Especially in the case of DROP or TRUNCATE, since allowing those to happen would obviate the need for the wraparound vacuum in the first place.

(For lovers of technical details: the lock mode is set in line 1590 of src/backend/commands/vacuum.c, it doesn't depend on auto or manual (on 12dev HEAD) . The cancellation is done in src/backend/storage/lmgr/proc.c based on a flag set in src/backend/storage/lmgr/deadlock.c. It is done there because the code that checks for deadlocks is a convenient place to check for being blocked by autovac.)


How to join by the most recent preceding date? Imagine I have a database with historic price data and a second table with dates

A new row was entered into the price history table every time the price changed.

How can I (efficiently) join the two tables so that I can get the price each customer had to pay on that day?

I need the syntax for teradata, thanks!

I'm afraid that I cannot guarantee 100% Teradata compatibility for my answer as there are, to my knowledge, no Teradata fiddles available. However, I have used PostgreSQL, one of the more standards compliant systems and there are no PostgreSQL specific constructs used in my answer (see the fiddle here), so I think we're good to go!

I glanced at the Teradata documentation for Window functions (LAG and LEAD) and for Common Table Expressions (CTEs) and comparing it with PostgreSQL's syntax (Window functions, CTEs), it all seems fairly vanilla.

First thing I did was CREATE and populate the tables. As an aside, you should avoid using images on StackExchange for the reasons outlined in this link. Use text for DDL and DML or, better yet, provide a fiddle - if you can't find a Teradata one (I looked and couldn't), use something like PostgreSQL which should get you most of the way there.

Another small note, it's not a good idea to name your fields using SQL keywords. Liberal use of the _ (underscore) character is a big help here.

CREATE TABLE price
(
  item_id TEXT NOT NULL,
  item_price SMALLINT NOT NULL,
  change_date DATE NOT NULL
);

INSERT INTO price
VALUES
('A', 249, '2014-09-01'),
('B', 129, '2014-09-01'),
('A', 299, '2014-08-25'),
('B', 139, '2014-09-25');

and

CREATE TABLE purchase
(
  customer_id SMALLINT NOT NULL,
  item_id TEXT NOT NULL,
  purchase_date DATE NOT NULL
);

INSERT INTO purchase
VALUES
(1, 'A', '2014-08-27'), (2, 'A', '2014-09-02');

I then ran the following queries - I've left in extraneous fields which aren't required for the final calculation so that my thought processes could be followed - using LAG and LEAD can be tricky and it's easy to mix up which field you should be comparing with which other one, so my policy is to leave them all in and let the OP (Original Poster - i.e. you) decide which you find useful and want to keep and which you wish to eliminate.

WITH pr_cte AS
(
  SELECT
    p.item_id,
    LAG(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_price,
    p.item_price,
    LEAD(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_price,
    LAG(change_date, 1, '2000-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_date,
    p.change_date,
    LEAD(p.change_date, 1, '2038-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_date
  FROM price p
  -- ORDER BY p.item_id, p.change_date -- not allowed in Teradata according to @dnoeth
)
SELECT * FROM pr_cte;

If you don't have a version with CTEs, you can always use a subquery.

Result:

item_id     lag_price   item_price  lead_price       lag_date  change_date  lead_date
      A                        299          249    2000-01-01   2014-08-25  2014-09-01
      A           299          249                 2014-08-25   2014-09-01  2038-01-01
      B                        129          139    2000-01-01   2014-09-01  2014-09-25
      B           129          139                 2014-09-01   2014-09-25  2038-01-01

Notice the use of the defaults 2000-01-01 and 2038-01-01 - I'm implicitly assuming that the prices before the specified dates in the price table go back to 2000 and that prices after will continue till 2038 when *nix time will explode! Obviously, you can change these for values more suited to your use case. I've left it up to you to provide default prices, again as per your use case.

Then, I ran:

WITH pr_cte AS
(
  SELECT
    p.item_id,
    LAG(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_price,
    p.item_price,
    LEAD(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_price,
    LAG(change_date, 1, '2000-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_date,
    p.change_date,
    LEAD(p.change_date, 1, '2038-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_date
  FROM price p
  -- ORDER BY p.item_id, p.change_date -- see above and comment by @dnoeth
)
SELECT
  pr.item_id, pr.item_price,
  pur.customer_id, pur.item_id, pur.purchase_date
FROM pr_cte pr
JOIN purchase pur
  ON pr.item_id = pur.item_id
  AND pur.purchase_date >= pr.change_date
  AND pur.purchase_date < pr.lead_date;

Result (again, cull fields as you see fit):

item_id     item_price  customer_id     item_id     purchase_date
      A            299            1           A        2014-08-27
      A            249            2           A        2014-09-02

Which is, AFAICT, the correct answer. You may want to test with edge cases which I may have overlooked - if there's a problem, get back to me. +1 for an interesting and challenging question and welcome to the forum!


Get the DDL for all indexes - Oracle I have about 5 schemas in my oracle 12c database for which I need the DDL for all the indexes for each of the schemas. DDL I have checked it on how to get the code from it, but for all the indexes and for each schema in one go.. is it possible?
If yes, help me out.!

Thanks.!

You can use dbms_metadata for this.

select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in ('USER_1', 'USER_2', 'USER_3', 'USER_4', 'USER_5');


Insert a sequence of number where some numbers could exist already / fill gaps? So before I make this completely in my main programming language I wanted to know, if there is any way to accomplish this with pure SQL? Also I think it would be a lot more performant.

MySQL Version 5.7.22-1-log

+----+-------+
| id | num   |
+----+-------+
| 19 | A1000 |
| 20 | A1001 |
| 21 | A1002 |
| 22 | A1003 |
| 23 | A1010 |
| 24 | A1011 |
| 25 | A1012 |
| 26 | A1013 |
| 27 | A1020 |
| 28 | A1030 |
| 29 | A1031 |
| 30 | A1032 |
| 31 | A1033 |
| 33 | A1034 |
+----+-------+
# structure
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| num   | varchar(25)      | NO   | UNI |         |                |
+-------+------------------+------+-----+---------+----------------+

So there are so numbers available between the ranges. To make things tricky, they are prefixed.

So I want to generate/insert e.g. exactly 50 numbers with prefix A and it should fill the free numbers between like A1004,A1005,A1006,A1007 etc.

I often use a table containing integers from 1 to (let's say) 1.000.000 because it's always useful with mysql. Assuming such table exists and is defined as:

CREATE TABLE `integers` (
    `n` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`n`)
) ENGINE=InnoDB

Then with this setup:

CREATE TABLE dbat (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
num VARCHAR(25) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY num (num)
);

INSERT INTO dbat (id, num) VALUES
(19, 'A1000'),
(20, 'A1001'),
(21, 'A1002'),
(22, 'A1003'),
(23, 'A1010'),
(24, 'A1011'),
(25, 'A1012'),
(26, 'A1013'),
(27, 'A1020'),
(28, 'A1030'),
(29, 'A1031'),
(30, 'A1032'),
(31, 'A1033'),
(33, 'A1034');

SELECT t.num
FROM (SELECT CONCAT('A', LPAD(i.n, 4, '0')) AS num
        FROM mta_info.integers AS i
        WHERE i.n >= 1000 AND i.n <= 9999) AS t
LEFT JOIN dbat ON dbat.num = t.num
WHERE dbat.id IS NULL
ORDER BY t.num ASC
LIMIT 50
;

Yields

num
A1004
A1005
A1006
A1007
A1008
A1009
A1014
A1015
A1016
A1017
A1018
A1019
A1021
A1022
A1023
A1024
A1025
A1026
A1027
A1028
A1029
A1035
A1036
A1037
A1038
A1039
A1040
A1041
A1042
A1043
A1044
A1045
A1046
A1047
A1048
A1049
A1050
A1051
A1052
A1053
A1054
A1055
A1056
A1057
A1058
A1059
A1060
A1061
A1062
A1063

Instantly executed

Having such integers tables is something every DBA should have when dealing with MySQL!