High ASYNC_NETWORK_IO when user locks computer with Excel open I have a user who gets in at 8AM and leaves at 6PM every day, and every day I see the same strange pattern in SolarWinds DPA.

It seems that Excel is refreshing the Query Table when the user locks their screen.  My questions are:

Is there any way to stop Excel from doing that?  I can't be the only DBA who has fought such problems.
Is there any way I can reproduce the problem on my machine?
Help finding Google keywords to search for a known issue... I've tried unsuccessfully

UPDATE

It occurred to me there could be other factors at play here, so here goes:

The file is on a Network-Attached Storage Drive shared among a work group.
I looked into various Network Interface Card powersaving feature issues that could be causing Excel to think the file handle is lost (???)
"Refresh data when opening the file" is checked under Connection Properties
This may be occurring on a Virtual Machine.

I have found one other user with a similar problem: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bcc35121-1575-4a7f-b82f-1c20f6fed58d/process-blocked-by-asyncnetworkio-status-blocks-other-queries?forum=sqldatabaseengine

ASYNC_NETWORK_IO can cause database contention issues if the results being sent to the client never spool anywhere (e.g., via an ORDER BY clause forcing a sort in tempdb).
If this issue is happening with Excel, then the problem is likely due to an Excel Database Connection refreshing on the background thread and the user simultaneously locking their computer during the background refresh.  When the background thread is complete, the computer is locked.  The issue is that in Win32, a locked computer means the user session object is not available.  When the user session object is not available, there is nowhere for the background thread to return results to, and it just hangs, waiting for Excel.exe to read the results.  Because the results never spool anywhere, the ASYNC_NETWORK_IO lock causes a livelock on any previously acquired locks.  In itself, ASYNC_NETWORK_IO is harmless.  It's the lock chain that is doing the damage.
To sum it up, this problem can happen under the following two conditions:

ASYNC_NETWORK_IO is present due to a slow reader (in my case, the slow reader is livelocked due to Win32 user session object unavailable)
The results are never spooled to some intermediary object, implicitly (e.g. ORDER BY triggering a sort in tempdb on underlying table, because the data is too big to sort completely in memory) or explicitly (INSERT results into a tempdb table, do SELECT on the tempdb table, which releases the livelock).

Two options:

If you don't have control over the client's slowness?

a. Write the query results to a temp table. Return the temp table to the user.
b. Also, don't forget to SET NOCOUNT ON at the top of the stored procedure, to avoid roundtrip delays due to DONE_IN_PROC TDS messages being sent to the client.

If you do have control over the client's slowness?

a. Check if the NIC is misconfigured or if the client is using a VM and the VM's NIC is miconfigured. See: https://www.reddit.com/r/sysadmin/comments/2k7jn5/after_2_years_i_have_finally_solved_my_slow/
b. Measure using Perfmon. Microsoft uses a heuristic of 10 for PerfMon Network Output Queue Length. See: https://technet.microsoft.com/en-us/library/aa997365(v=exchg.80).aspx
c. Add Query BeforeRefresh and AfterRefresh event handlers to instrument logging clues in the Worksheet to determine what the user is doing. In particular, see "XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events": https://support.microsoft.com/en-us/help/213187/xl2000-how-to-use-the-query-beforerefresh-and-afterrefresh-events
d. Write an Excel add-in that caches their SQL calls.
UPDATE 1/19/2018
I'm still dealing with high ASYNC_NETWORK_IO issues with Excel users.  I believe I have found more potential causes of this, but these are just theories at this point:

If you have multiple tables in the same spreadsheet, and the last table to the right has more rows than the tables to the left, this seems to cause Excel to "blink" a lot.  Correspondingly, in SolarWinds Database Performance Analyzer, queries on this spreadsheet that return the most rows and columns also have the highest ASYNC_NETWORK_IO problems. (Next steps for me are to reproduce this issue, but I'm not sure it's worth my time as I persuaded one user that they didn't need that huge result set at all for now.)

I found a comment on Experts Exchange ( https://www.experts-exchange.com/questions/25049176/Slow-queries-excuted-throug-network-ASYNC-NETWORK-IO.html ) where someone said 5,000 row results or higher are notorious for causing problems.  This appears to actually be a recommendation raised internally by Microsoft Dynamics NAV application, which is notorious for causing SQL Server instability due to high ASYNC_NETWORK_IO waits.  The error message the application raises is, "The number of records in the table exceeds the maximum number of 5000. Set a filter to decrease the number of records in the table. Exporting too many records at once can affect system performance."  But this is just a guess.

For SSIS Excel Destination tasks, if the user does not have permission to write to Temporary Internet Files folder, then it might just hang: https://stackoverflow.com/a/23523954/1040437  (Again, just a theory as I use this to try to collate all possible theories into one place.)


What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx? Can anyone point me to the correct documentation or additional permissions I need besides SA to do the following?

When I run it, I receive the following error:

Cannot find the object "#test" because it does not exist or you do not have permissions.

IF NOT EXISTS
(
SELECT
    name
FROM sys.server_principals
WHERE
    name = 'testlimiteduser'
)
BEGIN
    CREATE LOGIN [testlimiteduser] WITH PASSWORD=N'apassword', DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END

IF NOT EXISTS
(
SELECT
    name
FROM sys.database_principals
WHERE
    name = 'testlimiteduser'
)
BEGIN
    CREATE USER [testlimiteduser] FOR LOGIN [testlimiteduser] WITH DEFAULT_SCHEMA=[dbo]
END

IF NOT EXISTS
(
    SELECT 1
    FROM sys.database_principals AS p
    WHERE
        p.name like 'testlimiteduser_app'
)
BEGIN
    CREATE ROLE testlimiteduser_app
    GRANT EXECUTE TO testlimiteduser_app
    ALTER ROLE [db_datareader] ADD MEMBER testlimiteduser_app
    ALTER ROLE [db_datawriter] ADD MEMBER testlimiteduser_app
    ALTER ROLE testlimiteduser_app ADD MEMBER [testlimiteduser]
    ALTER ROLE db_owner ADD MEMBER testlimiteduser  --   https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA
END

GO

exec as user='testlimiteduser'

drop table if exists #test
create table #test (id int )
exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000')
select * from #test
revert;

Checking the documentation in https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA are fine, but it doesnt even appear db_owner works in my testing.

Making an account db_owner, even for [tempdb], is still risky. Fortunately, it's not necessary to do this in order to accomplish the goal. You can simply use Module Signing to allow a stored procedure residing in [tempdb] make use of the db_owner database role:

Initial Setup

This just gets us to the same state as the code provided in the question (minus the testlimiteduser_app role, which is unnecessary for this goal).

IF (DB_ID(N'NotTempDB') IS NULL)
BEGIN
    CREATE DATABASE [NotTempDB];
    ALTER DATABASE [NotTempDB]
      SET RECOVERY SIMPLE;
END;
GO

USE [NotTempDB];

IF (SUSER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE LOGIN [testlimiteduser]
      WITH PASSWORD = N'apassword',
      DEFAULT_DATABASE = [tempdb],
      CHECK_EXPIRATION = OFF,
      CHECK_POLICY = OFF;
END;
GO

IF (USER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE USER [testlimiteduser]
      FOR LOGIN [testlimiteduser];
END;

Basic Setup

We need a module that can be signed (ad hoc SQL will not work), but we are not signing it just yet.

Here we:

create the stored procedure
grant the ability to execute that stored procedure to the test user.

GO
CREATE OR ALTER PROCEDURE dbo.[UpdateStats]
(
  @TableName sysname,
  @RowCount INT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = CONCAT(N'UPDATE STATISTICS ',
    QUOTENAME(@TableName),
    N' WITH ROWCOUNT = ',
    @RowCount);

EXEC (@SQL);
GO

GRANT EXECUTE ON dbo.[UpdateStats] TO [testlimiteduser];
GO

TEST 1

Please note: is best to use EXECUTE AS LOGIN, not AS USER as LOGIN is not only more accurate in terms of what happens when the app really does connect to SQL Server, but doing USER will add restrictions that might skew the behavior / test results.

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

Module Signing Setup

Create the certificate
Sign the stored procedure with that certificate
Copy certificate (public key only) to [master]
Create the certificate-based login from it.

IF (CERT_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE CERTIFICATE [Permissions$UpdateStats]
    ENCRYPTION BY PASSWORD = 'Super Bass-o-matic ''76'
    WITH SUBJECT = N'Permission to exec UPDATE STATISTICS ... WITH ROWCOUNT = ...',
    EXPIRY_DATE = '2099-12-31';
END;

-- Associate permissions (via future cert-based login) to stored procedure:
ADD SIGNATURE TO dbo.[UpdateStats]
  BY CERTIFICATE [Permissions$UpdateStats]
  WITH PASSWORD = 'Super Bass-o-matic ''76';

-- Copy certificate (public key only) to [master]
-- and create the cert-based login from it.
DECLARE @CopySQL NVARCHAR(MAX) = N'
USE [master];
    CREATE CERTIFICATE [Permissions$UpdateStats]
      FROM BINARY = ' + CONVERT(NVARCHAR(MAX),
      CERTENCODED(CERT_ID(N'Permissions$UpdateStats')), 1) + N';

    CREATE LOGIN [Permissions$UpdateStats]
      FROM CERTIFICATE [Permissions$UpdateStats];
';

EXEC(@CopySQL);

TEST 2

We signed the stored procedure and associated it with a login, but we haven't yet given that login the necessary permission, so the results are the same.

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

Final Setup: Assign Permission to Login OR User

This was kept as a separate step only to make it very clear that it is indeed the module signing that is getting this to work. And you have two options (pick only one):

add the existing certificate-based login to the sysadmin instance-level role

ALTER SERVER ROLE [sysadmin]
  ADD MEMBER [Permissions$UpdateStats];

OR:
Create a User in tempdb from the certificate-based login, then add the new User to the db_owner database-level role

USE [tempdb];
IF (USER_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE USER [Permissions$UpdateStats]
      FOR LOGIN [Permissions$UpdateStats];

    ALTER ROLE [db_owner]
      ADD MEMBER [Permissions$UpdateStats];
END;

TEST 3

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- SUCCESS!!!!

REVERT;

Conclusion

The main differences between the options are:

Option 1

Good: no object in tempdb (that needs to be created upon each start of the SQL Server service)
Bad: sysadmin permission is more powerful (not exactly "least privileged")

Option 2

Good: db_owner is more contained than sysadmin, in terms of power
Bad: object in tempdb (that needs to be created upon each start of the SQL Server service)

The difference between sysadmin and db_owner` is minimal, if not even relevant here, since the only thing that can be done with either of those permissions is what the code in the stored procedure does (this is one of the main benefits of module signing: it's highly granular).

So on a practical level, the complexity of creating the User in tempdb and adding it to db_owner upon each start of the SQL Server service (which requires creating a stored procedure in master, marking it as a "startup procedure", and enabling the instance-level config option "scan for startup procs") is worse than making the login sysadmin.

If the DB needing the additional permission was a user database, then I would probably go with Option 2, even if only for the purpose of having it match more closely with the intended use, as opposed to their being any real concern over sysadmin vs db_owner.

For a detailed explanation of the steps taken to apply module signing, please see my post:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

For more information about module signing in general, please see my post:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


How to find MySQL users causing high load? I have a MySQL server with multiple websites running on it. These sites are mostly the same software, but since recently MySQL is under very high CPU load.

I'm pretty sure there is 1 MySQL user that is causing the higher load, but how can I identify the resources used per user?

I know I can limit the number of connections and number of queries per user, but before enforcing limits, I want to know what the current numbers are.

How can this be measured (in MySQL 5.7)?

The performance schema provides statistics aggregated by account (user + host), by user, and by host.

See for example table:

performance_schema.events_statements_summary_by_account_by_event_name

Reference manual
https://dev.mysql.com/doc/refman/5.7/en/statement-summary-tables.html

This table will show statement statistics (aka, queries) broken down per account ... anyone causing heavy traffic (high number of queries) or expensive computation (a few very expensive and long latencies queries) should be very visible in these statistics.

[Edit] Example of query:

select `USER`, `HOST`, SUM(COUNT_STAR) AS TOTAL
  from events_statements_summary_by_account_by_event_name
  group by `USER`, `HOST`
  order by TOTAL DESC;

will show how many queries each account do, ordered by count descending.


Visual Studio, GIT, and SQL Scripts I am trying to figure out a way to have all of our random SQL scripts that we write under source control. I am already using SSDT for the database project. This is not for these sort of scripts. It is for all the other random queries that we run over time. Before we put them in a stored procedure. Or the other scripts that just don't really fit as a SP.

How can I bring my non SSDT Sql scripts under source control using Visual Studio?

Sure you can, that's what I do with my projects. Have a look:

Create a new Project, I use SQL Server Database Project because I generally end up adding more than just scripts.

Add items. Scripts in this case.

Go the Team Explorer and select GitHub (or TFS if that's what you're going to work with)

I'm going to create a new Private Repository.

Done!

Now Push the changes...

Finish!


How to query row containing the given element inside a JSON column in Laravel PostgreSQL All I have is a PostgreSQL table that has a JSON column called agents.
See below:
SELECT agents FROM cnms_rosters;

Result:
      agents
------------------
 [1,13,3,16,15]
 [12,13,14,15,15]
 [11,73,55,16,44]
(3 rows)

This column contains the id of an agent. What I want to do is to query the row or rows that has the specific agent id.
How can I achieve that?
I am currently using Laravel framework. Is there any Eloquent approach in querying the data? If not, how to query it using DB::raw or other query builder?

Select id, a from (SELECT Id,
  json_array_elements(r.agents) A
   FROM Cnms_rosters r) ra
   Where A::TEXT = '13';

See http://sqlfiddle.com/#!17/eb400/19


What are object-relational databases, and why is this model necessary in spatial databases? Over in GIS SE, many of us use ESRI geodatabases. ESRI describes geodatabases as being object-relational.

What are object-relational databases, and why is it necessary to use this model in spatial databases?

It seems like they've taken something simple, the relational database model, and made it into something complicated. I'd like to understand what the benefit is.

I'm not a DBA or a developer, so layman's terms would be appreciated.

The relational model and the object-oriented paradigm
The state-of-the-art relational model, originated in 1970 by Dr. E. F. Codd, is applied science concerning the field of database administration. Its two solid pilars are first-order logic and set theory.
The object-oriented paradigm, as devised by Dr. Alan Kay, is an approach useful to build application programs. It has proven to be quite effective in the construction of graphic user interfaces that facilitate the interaction between end-users and information systems.
As noted, each of the two frameworks mentioned above serves a very specific purpose. When utilized to generate the appropriate kind of component, both can be of great help in a software development project.
Database administration, application programs and the term “object-relational”
Before the conception and development of the relational model, (a) the design, creation and administration of application programs was heavily mixed with (b) the design, creation and administration of the data (all this in an ad hoc fashion). There was no science available to handle the data in a general and sound manner. Dr. E. F. Codd was actually a programmer at IBM, and faced first-hand the difficulties caused by said circumstances. With his brilliance, practical experience and mathematical background he was capable of envisioning and developing an elegant and robust foundation to manage the data which, amid other substantial factors, permits handling said resource in an application program-independent manner.
I am not sure but, in case an “‘object-relational’ database management system” might exist, it would be an artefact onto which “‘object-relational’ databases” could be implemented. An “‘object-relational’ database” would be a device that was created by means of an entanglement of object-oriented constructs along with relational instruments.
It is hard to tell if a database management system of the nature described above can exist since, by introducing object-oriented constructs, many of the relational capabilities are put at risk (see the “Codd’s Twelve Rules” for relevant information), therefore it could hardly be considered relational even when it would be partly so.
In fact, (i) appending object-oriented tools to a relational database management system and (ii) using them in the relational databases implemented on top of it is entirely needless.
Nevertheless, there are “current” opinions that advocate, via object-oriented patterns, a mixture of design, creation and administration regarding (a) application programs and (b) databases. This can be interpreted as an invitation to make a regression to a pre-scientific (i.e., pre-relational) era as far as the databases part is concerned (because, naturally, there is not an object-oriented model related to data administration).
For its part, the relational model provides general and strong mechanisms to design (the logical structure), constrain (the values) and manipulate (with abstractions) the data. One of their benefits is that they are very simple (but never simplistic). In order to get the most out of the aforementioned mechanisms, a database administrator/operator must work, necessarily, with relations (usually declared as tables in a given SQL platform), and as you know, a relation or table is not part of the object-oriented paradigm (which neither provides data integrity constraints nor general data manipulation operations, and it is not supposed to do so).
The power of a relational language lies in its expressive capabilities, not in its computational aspects. Roughly speaking, when following relational methods one declares the structure of the things of interest (the way they are, their structure), while with an object-oriented language (e.g., Smalltalk) one should deal mainly with the behaviour of the things of significance (how they do what they do, the processes they carry out), which is paramount with respect to programming an application.
One of the multiple advantages of a relational database is that, as it has to be designed independently of the languages used or paradigms followed at the application programming stage, it can work along with multiple languages and/or application programming paradigms and/or multiple application programs at the same time.
That being said, a relational database must to be built by a designer who follows relational principles, therefore the construction of (1) a particular database onto (2) a certain relational database management system does not automatically grant that particular database the relational label.
The ArcGIS geodatabase
With the intention to help in the understanding of the arcgis.com links you include in your question, I consider that when the term geodatabase is used, it is actually a contextual designation for a complete geographic information system that may include

one or more proper databases built on top of one or more database management systems, and
one or more application programs that work along with said database(s).

In that respect, let us take a look at the architecture of the proper database(s) of a geodatabase, where it is stated that:

The geodatabase storage model is based on a series of simple yet essential relational database concepts and leverages the strengths of the underlying database management system (DBMS). Simple tables and well-defined attribute types are used to store the schema, rule, base, and spatial attribute data for each geographic dataset. This approach provides a formal model for storing and working with your data. Through this approach, structured query language (SQL)—a series of relational functions and operators—can be used to create, modify, and query tables and their data elements.

So that somewhat suggests that the data is being handled independently of the application programs that access it, and it would be very valuable of course.
Then, this page contains the following title (in the form of an assertion) and paragraph:

The geodatabase is object relational
The geodatabase employs a multitier application architecture by implementing advanced logic and behavior in the application tier on top of the data storage tier (managed within various database management systems [DBMS], files, or extensible markup language [XML]). The geodatabase application logic includes support for a series of generic geographic information system (GIS) data objects and behaviors such as feature classes, raster datasets, topologies, networks, and much more.

Which seems to indicate that the objects behaviour (the way in which the application program objects act) is handled where it should be handled, i.e., at the application program level (or “tier”, as described there).
Further, back in the geodatabase architecture page, an identical title and a paragraph that is very similar to the one brought up above are introduced as follows:

The geodatabase is object relational
The geodatabase is implemented using the same multitier application architecture found in other advanced DBMS applications; there is nothing exotic or unusual about in its implementation. The multitier architecture of the geodatabase is sometimes referred to as an object-relational model. The geodatabase objects persist as rows in DBMS tables that have identity, and the behavior is supplied through the geodatabase application logic. The separation of the application logic from the storage is what allows support for several different DBMSs and data formats.

An excerpt that catches my attention in a particular way is “the geodatabase objects persist as rows in DBMS tables that have identity”, which is misleading because the tables (i.e., relations) of a relational database retain rows (i.e., tuples) that represent assertions that carry a specific meaning provided by a certain business domain predicate (that can be used to define an entity type), therefore it does not “persist” objects. Moreover, since an essential feature of an object is its behaviour (basically, its methods), it would be very interesting to know how it is “persisted” in a relational database.
On the other hand, the significant fragment “the separation of the application logic from the storage is what allows support for several different DBMSs and data formats” appears to stress the enormous relevance of handling the data separately from the application program.
Conclusion
My conclusion is that an ArcGIS geodatabase is neither (a) an object-relational database nor (b) an object-relational database management system. It may well be, as previously mentioned, a complete geographic information system that consists of (i) one or more actual databases (some of which could be more or less relational) and (ii) one or more application programs (some of which could be more or less object-oriented) accessing said databases.
Perhaps that is why it is contextually called “object relational”.


Bypass MS Access 2GB file size limitation I am creating a MS Access *.accdb database with multiple tables and specific relationships.

This database will be used for a one time data transport to another system, containing order of a few million entries. A test with 2 million dummy entries already reaches about 1.8GB file size. Since MS Access has a built in limit of 2GB per database file I am searching for solutions to bypass this limit.

I came up with two ideas so far:

Split the database into two files, continuing the indexing during the transition from file1 to file2, taking care of independence for the entries in related tables.

file1_table1 (indices 1..1000)
file2_table1 (indices 1001..N)

Export the tables to different files, using one master interface file

file1_table1
file2_table2
file3_table3
masterfile (linking table1..3 and creating table relationships)

I would like to get some feedback on these ideas concerning concept, performance and usability or if there are any other ideas about this.

Please note that I want to stick to MS Access, building up a SQL Database would be overkill, since it will be used for a one-time transport only.

The data is distributed over several sources (MS Access .accdb, Excel Sheets *.xlsx, delimited files *.csv) and is analyzed, merged and prepared as a temporary one-time-use MS Access database, then transformed into a working database based on SQL, which includes a lot more than what I am preparing. So it's a classical ETL process.

The "Compact and Repair" command won't help, since it is really just the large amount of data, not a large amount of operations leaving behind a lot of junk. The "Split an Access database" procedure only seems to separate Queries/Forms/Reports from the backend file containing the tables, but I need to split the tables themselves, leaving me with the problem of consistency between the split files. This is not an issue with separating the interface (front end) and data back end; I need to split the data backend itself due to the large amount of data.

You can't bypass the built-in limitation of an Access database. You can however circumvent the built-in limitation by using built-in features like splitting the database objects between multiple *.accdb files and referencing them accordingly.

The Access 2016 Specifications state:

  Total size for an Access 2016 database (.accdb), including all database objects and data:

  2 gigabytes, minus the space needed for system objects.

  Note: You can work around this size limitation by linking to tables in other Access databases. You can link to tables in multiple database files, each of which can be as large as 2GB.
  Tip:  For more information on reducing the size of your database, see Help prevent and correct database file problems by using Compact and Repair.

You can even go a step further and, as you have already mentioned, split the data and the interface.

Split an Access database

  Consider splitting any database that several people share over a network. Splitting a shared database can help improve its performance and reduce the chance of database file corruption.

  After you split database, you may decide to move the back-end database, or to use a different back-end database. You can use the Linked Table Manager to change the back-end database that you use.

Because it is Microsoft Access, I would recommend sticking with the supported options rather than trying another solution that might or might NOT work.

We used to have an application that had the interface (FE) and data (BE) split. No noticeable performance difference, when hosted on a file server. However, when the BE reached 2 GB a write corrupted the BE and the data was unrecoverable, that was the time we decided to switch to SQL Server Express and then later to SQL Server. I don't think there should be a noticeable performance impact when you access multiple BE files containing the different tables. But having the BE on file shares will generally be slower than storing them locally.


InnoDB create table error: "Row size too large" We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing that?"; let's just assume that we are addressing this).

We use MySQL 5.1.48 Community RHEL5 with InnoDB plug-in 1.0.9 on Linux.

When using MyISAM we never encountered table size limits of max columns or max row length (during investigation we have hit the max columns limit at 2598 (the 2599th causes error 1117). With InnoDB we are hitting limits. These limits are manifesting when creating the table (no data insertion) as:

  ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

I am looking for answers to the following:

What is the detailed formula for determining row size particulalry when using lot's of v/v/b/t columns? I've tried a few different formuals using varchar(N) columns (where N is between 1 and 512), the UTF8 charset (*3), and as many columns as the table will take until failure. None of the combos I've tried give values that match with actual test results.
What other 'overhead' must I consider when calculating the row size?
Why does the error message change from 8126 to 65535 when I go from creating tables with varchar(109) columns to varchar(110) columns?

The answers to your questions are complex, because they vary by InnoDB file format.  Today, there are two formats, called Antelope and Barracuda.

The central tablespace file (ibdata1) is always in Antelope format.  If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.

Basic points:

One 16KB page of InnoDB data must hold at least two rows of data.  Plus each page has a header and a footer containing page checksums and log sequence number and so on.  That's where you get your limit of a bit less than 8KB per row.
Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.
Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit.  In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page.  Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.
Variable-size data types are also prefixed with 1 or more bytes to encode the length.  And InnoDB row format also has an array of field offsets.  So there's an internal structure more or less documented in their wiki. [EDIT] Dead link - here looks better now.

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

I also have to comment that I've never seen a well-designed table exceed the row size limit.  It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.


Simplifying the "repetitive" code of a query involving UNION ALL, WHERE and LIMIT How can I simplify the code of the query shown below? Requirements are always the same as I describe as follows:

(select * from questions where points = 3 and type = 1 order by rand() asc limit 6)
union all
(select * from questions where points = 2 and type = 1 order by rand() asc limit 4)
union all
(select * from questions where points = 1 and type = 1 order by rand() asc limit 2)
union all
(select * from questions where points = 3 and type = 0 order by rand() asc limit 10)
union all
(select * from questions where points = 2 and type = 0 order by rand() asc limit 6)
union all
(select * from questions where points = 1 and type = 0 order by rand() asc limit 4)

Is there any way to do it without UNION ?

I am just curious if there is a faster/more efficient way of doing it. SQL Server or MySQL, I haven't decided yet, because I am at design stage right now.

You can use the ROW_NUMBER() analytical function to generate row numbers for each partition of (points, type):

SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
FROM
  questions

And then you can compare rownum to a certain value that depends on points and type:

WHERE
  rownum <= some_expression

Of course, you will need to use nesting in order to be able to reference rownum like that:

SELECT
  ...
FROM
  (
    SELECT
      ...
      ROW_NUMBER() ... AS rownum
    FROM
      ...
  ) AS derived
WHERE
  rownum <= ...

And you need to keep in mind that because of the nesting the rownum column becomes part of the derived table's column set – so, specifying * as the main SELECT's column list will include the rownum column as well. If you want it out of the result, you will have to list each questions column explicitly:

SELECT
  question_id,  /* or whatever the PK column is going to be called */
  points,
  type,
  ...  /* other "questions" columns */
FROM
  (
    ...

Taking all of the above points into account (the last one in particular), I am unsure if the resulting query can be called a simplification of the original, but here is my attempt anyway:

SELECT
  question_id,
  points,
  type,
  ...
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
    FROM
      questions
  ) AS derived
WHERE
  rownum <= CASE
              WHEN points = 3 AND type = 1 THEN 6
              WHEN points = 2 AND type = 1 THEN 4
              WHEN points = 3 AND type = 1 THEN 2
              WHEN points = 3 AND type = 0 THEN 10
              WHEN points = 2 AND type = 0 THEN 6
              WHEN points = 1 AND type = 0 THEN 4
            END
;

The CASE expression returns a row limit for each required partition and a NULL for any other, thus effectively providing a filter to eliminate all irrelevant rows (the rows where points and type have values other than those mentioned in the conditions). This kind of filter, however, is not pushed down to the underlying questions table and, therefore, does not prevent the query from generating row numbers for all combinations of (points, type) before filtering out those that are not needed. You can avoid the redundant work by adding an explicit filter to the questions table like this:

  ...
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
    FROM
      questions
    WHERE
      points IN (1, 2, 3)
      AND type IN (0, 1)
  ) AS derived
WHERE
  ...

Alternative

Alternatively you can rewrite the entire query in a different way, such that rows are filtered and row limits assigned at the same time. If you use a virtual table like this:

(
  SELECT 3, 1,  6 UNION ALL
  SELECT 2, 1,  4 UNION ALL
  SELECT 1, 1,  2 UNION ALL
  SELECT 3, 0, 10 UNION ALL
  SELECT 2, 0,  6 UNION ALL
  SELECT 1, 0,  4
) AS limits (points, type, rowlimit)

you can join questions to it and thus have both a filter and a way to assign row limits to each partition. The full query will then look like this:

SELECT
  question_id,
  points,
  type,
  ...
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
    FROM
      questions AS q
      INNER JOIN
      (
        SELECT 3, 1,  6 UNION ALL
        SELECT 2, 1,  4 UNION ALL
        SELECT 1, 1,  2 UNION ALL
        SELECT 3, 0, 10 UNION ALL
        SELECT 2, 0,  6 UNION ALL
        SELECT 1, 0,  4
      ) AS limits (points, type, rowlimit)
        USING (points, type)
  ) AS derived
WHERE
  rownum <= rowlimit
;

You can then go further and make the limits dataset an actual table in your database. You have mentioned that requirements are not going to alter, but perhaps knowing that you can configure the requirements in the table will change that perspective.

Product-related notes

You mention that you have not decided in which SQL product this is going to be used. Here are some product-related notes to consider.

Both variations of the solution will work in MySQL 8.0 or newer version. Version 8.0 is the minimum because that is where support for ROW_NUMBER() was first introduced in MySQL.
The other product you mentioned, SQL Server, has supported ROW_NUMBER() since version 2005 but other aspects of the solution will require changes in order to make it work in SQL Server.

2.1. You are using ORDER BY RAND() to randomise the rows selected. In SQL Server, the RAND() function is a runtime constant†, therefore you will need to use a different method; ORDER BY NEWID() could be one:

  ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY NEWID()) AS rownum

2.2. The JOIN ... USING syntax is not supported in SQL Server. You will need to replace it with the JOIN ... ON one. Because of this switch, you will also need to rewrite the nested SELECT column list. The reason is, USING automatically suppresses duplication of the same-name columns used for joining, points and type, when you are using * in the SELECT clause. The ON operator has no such effect, therefore you will need to rewrite the derived query like this:

  (
    SELECT
      q.*,
      limits.rowlimit,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY NEWID()) AS rownum
    FROM
      questions AS q
      INNER JOIN ( ... ) AS limits (points, type, rowlimit)
        ON q.points = limits.points AND q.type = limits.type
  ) AS derived

2.3. While not a required change per se, you can take advantage of SQL Server's support of the VALUES constructor as a means of defining a derived table to make the limits definition more compact:

  (
    VALUES
      (3, 1,  6),
      (2, 1,  4),
      (1, 1,  2),
      (3, 0, 10),
      (2, 0,  6),
      (1, 0,  4)
  ) AS limits (points, type, rowlimit)

†The result of the function will not change for the duration of the SELECT statement's execution.


Safe to use the same temporary table name in parallel transactons? Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. The name itself is not important, but I would like to safe myself the trouble of generating random names for every concurrent query that needs such intermediate table.

Temporary table is only used in different SELECT queries. It will be dropped at the end of each transaction with the ON COMMIT DROP clause.

PostgreSQL documentation states:

  Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names.

It is not clear to me if this applies to temporary tables too.

documentation means:

if you have permanent table TABLE_A
and you create a temporary table with the same name TABLE_A,

a permanent table will be not visible for your code.

But you could request data from the permanent table by add schema to name - schema_name.table_name.

Answering to your question - yes, it is safe.


T-SQL Naming Standard Is there a recommended (official or not) style from Microsoft regarding naming and casing in T-SQL?

If there isn't one from Microsoft then is there one with broad acceptance?

There's no broad standard that I've seen used or heard widely recommended.  Some recommend prefixing tables with tbl but some are very against that.  Some, like myself, prefer to name the ID column simply so you always know what the ID column of any table will be called ID while others, like my coworker, prefer to put the tablename (ie - serverid) so what it's called in another table matches the column name in the parent table.

The most important thing is that the scheme you use remains consistent, especially within the same database.  Most DBAs can pick up on the naming format as long as it doesn't change within an environment.


How to use variables in procedure (not function)? As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:

declare a variable
select a value into a variable
insert the variable into another table
do this in a transaction
do this in a stored procedure
roll back if necessary

CREATE PROCEDURE test_variable()
LANGUAGE SQL
AS $$
    BEGIN;
        DECLARE some_id INTEGER;
        SELECT nextval('some_sequence') INTO some_id;
        INSERT INTO some_table (some_column) VALUES (some_id);
    END;
$$;

The above is not working out for me. When I search for solutions, there's so many different variables involving functions, $$, declaration, thing not returning anything; can't seem to find a simple example; I just need a clear example of the syntax.

First of all, SQL functions or procedures (LANGUAGE SQL) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures alike. (Maybe other PLs, too.)

Like dezso commented, do not confuse plpgsql BEGIN (starting a block) with SQL BEGIN; (starting a transaction). Neither is allowed in an SQL function. The latter is allowed in an SQL procedure.

To use variables, you need LANGUAGE plpgsql (or one of the other PLs). To give you a clear example:

CREATE PROCEDURE test_variable()
 LANGUAGE plpgsql AS
$$
DECLARE
   _some_id int;
BEGIN
   SELECT nextval('some_sequence') INTO _some_id;
   INSERT INTO some_table (some_column) VALUES (_some_id);
END
$$;

The exactly same body would work for a function as well.


What does exec(text) do? I just found the following function in two RDS PostgreSQL 9.3 DBs:

--
-- Name: exec(text); Type: FUNCTION; Schema: public; Owner: adam
--

CREATE FUNCTION exec(text) RETURNS text
    LANGUAGE plpgsql
    AS $_$ BEGIN EXECUTE $1; RETURN $1; END; $_$;

ALTER FUNCTION public.exec(text) OWNER TO adam;

I am pretty sure I did not write this function, and since exec seems like a security hazard (does it simply execute commands?) It would like to know what is it all about. Searching the method signature didn't help me.

What does this function do?
Is it a security risk of some sort?
Which program/framework might have created it?

It's a totally pointless function that executes arbitrary SQL. It isn't SECURITY DEFINER so the only risk I think it can pose is if you allow users to run arbitrary SQL predicates or call arbitrary functions (in which case you're probably already stuffed) but try to block them from running any command they want.

As you guessed, it just executes the SQL input using PL/PgSQL's dynamic SQL facilities.

Oh, it'd also hide the statement text from simple log_statement analysis, auto_explain etc, as they only look at top level statements, not statements run via PL/pgSQL.

Personally I'd drop/rename it, and see what complains.


Tables locked(database locked) in MYSQL We are using MYSQL INNODB,After inserted one record in table,Locked database operations resulted in proxy and application threads to increase that ultimately caused the proxy server to not accept connections.I would like to know the root cause for this tables locking?

In my personal opinion debugging table locks in MySQL can be a pain in the *ss sometimes.
Some queries that may give you some insights are:

Get a list of the locked tables

SHOW OPEN TABLES WHERE In_Use > 0;

Get a list of all processes using MySQL resources with corresponding queries

SHOW FULL PROCESSLIST;

Get specific information about the InnoDB storage engine including deadlocks

SHOW ENGINE INNODB STATUS;


MongoDB doesnt start normally? I want to start mongo on terminal but it gives connection error like:

MongoDB shell version: 2.4.5
connecting to: test
Sat Jan  4 10:18:16.779 JavaScript execution failed: Error: couldn't connect to server 127.0.0.1:27017 at src/mongo/shell/mongo.js:L112
exception: connect failed

I run the following commands:

rm /var/lib/mongodb/mongod.lock
mongod --dbpath /var/data/mongodb --repair
restart the mongodb

But unable to start mongo.
When I run the following command:

mongod --profile=1 --slowms=20 --dbpath /var/data/mongodb --fork --logpath /var/log/mongodb/mongodb.log\

It works fine but log is not created inside logger.

Please help me to fix this issue this is so wierd issue for me.

There are two separate processes that you are referring to.

mongod is the command that starts the MongoDB server process.  It listens on a port (27017 by default) and you can connect to it with mongo shell or with your driver from an application.
mongo is the mongo interactive shell.  It tries to connect to a port (27017 by default) to talk to the mongod server process.  It will not be able to connect to a port that does not have a running mongod process listening on.

You need to start mongod first.  Whether you start it logging to the console or to a log file (--logpath) it needs to be started and running successfully.  If you provide a --logpath argument then you can also provide a --fork argument which will detach it and run it as a separate process, rather than attached to your console.

Once you have mongod successfully running and waiting for connections, you can now run mongo program to start up the shell and connect to the server.


SQL server Virtual account and folder permissions How does SQL server 2012 still work after changing the Service account to a domain account or even a local account. For example: as mentioned in MSDN

https://msdn.microsoft.com/en-us/library/ms143504.aspx#VA_Desc

This folder Instid\MSSQL\data has full control privileges to the virtual account NT service\MSSQLSERVER. But I am confused how SQL server still starts after changing the service account to a domain or local account as this new service account will not be having privileges to this particular folder unless we explicitly provide it. And from what I tested, SQL Server fails to start if you remove NT service\MSSQLSERVER from Instid\MSSQL\data folder's permissions.

So does this mean any account which is a SQL server service account is automatically added to NT service\MSSQLSERVER group?, How does this work?

As long as you change the service account being used by using the SQL Configuration Manager, then it will configure all the necessary permissions for the new service account.

https://msdn.microsoft.com/en-us/library/ms143504%28SQL.110%29.aspx#Serv_SID

Note that the mechanism that happens under the hood to grant these permissions has changed with the different versions of SQL, according to this discussion:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e6bb2de-8fd0-45de-ab02-d59bbe05f72e/servicedatabase-accounts-nt-servicemssqlserver-nt-servicesqlserveragent-what-are-they-for?forum=sqlsecurity


Connecting SQL Server 2012 to MySQL I have 2 devices, a server computer installed with SQL Server 2012, and a raspberry pi with MySQL installed. I have data in MySQL tables. How can I connect SQL Server 2012 to MySQL so I can save that data?

One easy way to do this is with HeidiSQL, which can connect to SQL Server and MySQL and is free and open source (https://www.heidisql.com/). Once you set up both connections, you can use the Export Database As SQL feature to transfer data directly between databases.

In the Session Manager, set up the connection to the MySQL and SQL Server computers
Connect to the MySQL server, right-click on the server in the browser on the left, and select "Export Database As SQL".
in the table list, select the table(s) you would like to export
in the Output: on the right, you will see a list of all servers you have configured in the Session Manager. Select the SQL Server 2012 instance.
You will probably want to keep the database the same so leave it as [Same as on source server]
You likely will want to check the create boxes for both database and tables if this is the 1st time you are transferring data
If you want to transfer the data and not just the table structure, in the Data: dropdown select the option that is best for you. I usually use Delete + insert (truncate existing data), but your needs may be different.
Click Export in the bottom right

That should do it.


Database backup issue in SQL Server For security reason I always took back up of my database. I took full backup in each month. And differential in each week. I used to save each back up file in different name. I lost my latest full back up file. But I have my latest differential back up file. So when I tried to restore my differential back up with the previous full back up , I am getting an error that

  This differential backup cannot be restored because the database has not been restored to the correct earlier state

My scenario:

Took Full Backup        - File is with me
Took Differential       - File is with me
Took Another full       - Lost this file
Differential file       - is with me

So can I restore 4th one with 1st one. With out the 3rd ?

Is there any solution for this scenario? Can I restore it without losing data?

I am using SQL Server 2008 R2

Since you said you are beginner and is in learning phase I would give you lot of good articles to learn about SQL Server backup.

To start with main question as already mentioned in my comment

  So can I restore 4th one with 1st one. With out the 3rd ?

Differential backup only relates to previous full backup you have taken so NO you cannot restore 4th differential backup if 3rd full backup is lost. Also since as you said the database is in simple recovery model there is no chance of taking transaction log backup.

  [To understand] Whether there any way if it is not simple ?

If your database were in full recovery you could have taken Transaction log backup. The good thing with transaction log backup is that Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.

So you could have restored first full backup and all log backups, in serial order, taken after the full backup. This would hold true even if 3rd full backup is lost.

You must read Understanding Backups in SQL Server there is a Backup myth buster series from PR related to backups you must also read that.

Feel free to ask if any doubts I would add the comments in this answer


SELECT operation with four joins results in bad performance I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places.
Resources

Query:
  SELECT DISTINCT doers.id
  FROM doers
  JOIN doer_locations dl
      ON dl.doer_id = doers.id
  JOIN job_places jp
      ON (jp.lat - 0.3147625620715557) < dl.lat
     AND (jp.lat + 0.3147625620715557) > dl.lat
     AND (jp.lng - 0.5001626620527362) < dl.lng
     AND (jp.lng + 0.5001626620527362) > dl.lng
  LEFT JOIN job_realizations jr
      ON jr.job_place_id = jp.id
     AND jr.status IN (1, 2, 3, 4)
  LEFT JOIN job_realizations jrpd
      ON jrpd.job_place_id = jp.id
     AND jrpd.doer_id = doers.id
     AND jrpd.status IN (1, 2, 3, 4)
  WHERE (jp.job_id = 1 AND doers.id IS NOT NULL)
  GROUP BY doers.id, jp.id
  HAVING COUNT(DISTINCT jr.id) < jp.realizations_per_place
     AND COUNT(DISTINCT jrpd.id) < jp.realizations_per_place_per_doer

Depesz explain

Raw explain analyze

Simplified Schema

Consideration
I'm not sure if I read the explain correctly but it seems it loses on performance especially when it calculates stuff on the run also HAVING COUNT(DISTINCT) seems pretty expensive.
Additional information
The type of both the lat and long columns is float.

--I think this is mandatory for your query performance:
    --Because you do joins using this columns from parent to child

    create index on doer_locations(doer_id);
    create index on job_realizations(job_place_id);
    create index on job_realizations(doer_id);

    --Maybe very big and slowdown other operations...
    --create index on job_realizations(lat);
    --create index on doer_locations(lat);
    --create index on job_realizations(lng);
    --create index on doer_locations(lng);

    --Maybe not mandatory:
    create index on job_realization(realizations_per_place);
    create index on job_realization(realizations_per_place_per_doer);

SELECT DISTINCT doers.id FROM doers
    JOIN doer_locations dl ON dl.doer_id = doers.id
    JOIN job_places jp ON (jp.lat - 0.3147625620715557) < dl.lat AND (jp.lat + 0.3147625620715557) > dl.lat AND (jp.lng - 0.5001626620527362) < dl.lng AND (jp.lng + 0.5001626620527362) > dl.lng
    LEFT JOIN job_realizations jr ON jr.job_place_id = jp.id
    LEFT JOIN job_realizations jrpd ON jrpd.job_place_id = jp.id AND jrpd.doer_id = doers.id
    WHERE (jp.job_id = 1 AND doers.id IS NOT NULL)
    GROUP BY doers.id, jp.id
    HAVING COUNT(DISTINCT jr.id) < jp.realizations_per_place AND COUNT(DISTINCT jrpd.id) < jp.realizations_per_place_per_doer

Please try and if it solve your problem select it as the right answer.


SQL Server 2012 database backup successfully report but no backup file I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must be stored. But SQL Server logs history about this maintenance plan are successful.

What is the problem?

I am using SQL Server 2012. The operating system is Windows Server 2008 R2.

Thank you for paying attention.

The msdb database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.

This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+.  Just tested in 2012.

/*
Find Where DB Backups Went Physical Location
For last two days.
backupset.type
D --> FULL
I --> DIff or incrimental
L --> Log backups
*/

DECLARE @dbname     sysname
SET @dbname = ''

SELECT
     @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [Backup Date]
    ,msdb.dbo.backupset.user_name
    ,datediff(second, msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
    ,msdb.dbo.backupmediafamily.physical_device_name [File Location]
    ,msdb.dbo.backupset.type
FROM
    msdb.dbo.backupmediafamily,
    master.sys.sysdatabases
    LEFT OUTER JOIN
    msdb.dbo.backupset
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE
    msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'
ORDER BY
    master.sys.sysdatabases.name
    ,msdb.dbo.backupset.backup_start_date
    ,msdb.dbo.backupset.backup_finish_date
    ,msdb.dbo.backupmediafamily.physical_device_name
    ,msdb.dbo.backupset.type


OEM 11g alerts and notifications arrive together I setup the OEM with the Idea to receive Critical messages (emails when the DB, listener, agents and host are down/up) but it doesn't seem to be working.  If I "shutdown abort" the database I wont received any message until I put it up and running, so I receive all the messages together.

Please see image Bellow, that's how I set the alerts, I tested the Email sending and it's working.

Any Idea what it could be?

And that is why Enterprise Manager Database Control is inappropriate for this kind of monitoring.
Enterprise Manager Grid Control/Cloud Control runs on a seperate host, with a seperate repository database, and that is the tool for this job.


Error while importing sql - Specified key was too long; max key length is 1000 bytes I am trying to import an sql file which shows me the following error:

Error
SQL query:

-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS  `users` (

 `uid` INT( 11 ) NOT NULL AUTO_INCREMENT ,
 `uemail` VARCHAR( 255 ) NOT NULL ,
 `upassword` VARCHAR( 255 ) NOT NULL ,
 `uname` VARCHAR( 255 ) NOT NULL ,
 `uemailh` VARCHAR( 255 ) NOT NULL ,
 `uclass` TINYTEXT NOT NULL ,
 `regno` VARCHAR( 8 ) NOT NULL ,
 `absencecount` VARCHAR( 255 ) NOT NULL DEFAULT  'a:10:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;i:4;i:0;i:5;i:0;i:6;i:0;i:7;i:0;i:8;i:0;i:9;i:0;}',
 `internalmarks` VARCHAR( 500 ) NOT NULL DEFAULT  's:73:"["0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0"]";',
PRIMARY KEY (  `uid` ) ,
UNIQUE KEY  `uemail` (  `uemail` ,  `uname` ) ,
KEY  `regno` (  `regno` )
) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT =2;

MySQL said: Documentation

#1071 - Specified key was too long; max key length is 1000 bytes

I've tried to create a new  database in my local machine everything works fine. But, while importing it in the remote server it shows the above error. Any Idea?

According to the MySQL Documentation on CREATE INDEX:

  Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

My guess would be the character set you are using in conjunction with

UNIQUE KEY  `uemail` (  `uemail` ,  `uname` ) ,

Please note that both of those columns are VARCHAR(255). Together their maximum character length could be 511 (255 characters X 2 + 1 byte for VARCHAR length management). Since UTF8 is a multibyte character set, 511 * 2 = 1022. That's way past InnoDB's limit of 767 bytes. Even if you convert the table to MyISAM, it's still past 1000 bytes.

I would suggest drastically reducing the widths of uemail and  uname.


How does the optimizer in Oracle Database use I/O statistics? I would like to help Oracle's query optimizer make better-informed decisions. I have been unable to find much information on how the optimizer determines the cost of I/O operations, other than unsourced remarks that it uses the time that it takes to retrieve a single data block. Here are some things that I would like to know:

Is it possible to view the current I/O benchmarks used by the optimizer?
Is it possible to trigger an update of those benchmarks?
Does the optimizer account for the current I/O cost of an operation? For example, if there is heavy concurrent load on the storage device causing a reduction in performance, does it consider that the relative cost of I/O will temporarily be higher?
Does the optimizer consider latency/random-access performance versus throughput/sequential performance?
If you have data files on multiple storage devices with different performance characteristics, does the optimizer have and apply device-specific costs?

You can see currently used statistics in SYS.AUX_STATS$.
DBMS_STATS.GATHER_SYSTEM_STATS collects system statistics. Running it with gathering_mode='NOWORKLOAD' will capture IO related stats of the server. You usually also run it under workload ("START/STOP", "INTERVAL").
(4,5)  Collecting system statistics during typical load should take care of potential problems you mentioned.
In addition , (4) will be affected by optimizer mode (ALL_ROWS vs FIRST_ROWS)

Also, with procedures in dbms_stats packages it's possible to collect stats multiple times, export (EXPORT_SYSTEM_STATS ), import (EXPORT_SYSTEM_STATS), or delete it (DELETE_SYSTEM_STATS).


SSDs and 64K sectors SSDs tend to have a natural 4K sector size as far as I know, but would it be beneficial to format Windows cluster size at 64K? This is good practice for spinning rust, but is it relevant on SSDs?

Yes, 64KB is still the current recommendation as it has to do with partition alignment.  To quote Jimmy May from the the SQL CAT team:

  Partition alignment remains a best practice for all versions of
  Windows Server as well as SQL Server, including SQL Server 2012 & SQL
  Server 2014.  No exceptions.  Period.

As part of the Best Practice guide referenced in that article, the following line is quite explicit:

  The file allocation unit size (cluster size) recommended for SQL
  Server is 64 KB

As for a SSD vendor's perspective, I point you to Argenis Fernandez's blog post about this very topic.  Pure even recommends 64KB for NTFS block size, so I think it's safe to assume this is a pretty universal truth.


How can I change the connection descriptor used by Oracle Enterprise Manager? I've recently changed the SERVICE_NAME of my 11g database and Enterprise Manager can no longer connect to it:

  2014-01-08 10:36:06,047 [HTTPThreadGroup-3] ERROR eml.OMSHandshake
  processFailure.806 - OMSHandshake failed.(AGENT URL =
  https://Test9.lab.nyc-14.knoa.com:3938/emd/main)(ERROR =
  INTERNAL_ERROR)(CAUSE =java.sql.SQLException: Listener refused the
  connection with the following error: ORA-12514, TNS:listener does not
  currently know of service requested in connect descriptor The
  Connection descriptor used by the client was:
  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Test9.lab.nyc-14.knoa.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test9.lab.nyc-14.knoa.com))))

The SERVICE_NAME in there is indeed the old one. I've updated NETWORK\ADMIN\tnsnames.ora but that didn't fix it. Where does Enterprise Manager read the connection descriptor from?

I'm guessing because you haven't specified what version of OEM you're using.

If you're using OEM 12C, go to the DB home then click on "Oracle Database", "Target Setup", "Monitoring Configuration".  You can change the SID there.


T-SQL command to log in as a different user? Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?

You can use the EXECUTE AS command:

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


RedGate SQL Prompt - Cursor Snippet mistake Using RedGate's SQL Prompt & loving it.  But I used the "curff" snippet for the first time today ("Fast-forward read-only cursor"), and it produced the following TSQL (which I've filled in to make it a concrete example):

DECLARE @variable INT
DECLARE Curse CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT ID FROM #TempTable

OPEN Curse
FETCH NEXT FROM Curse INTO @variable

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM Curse INTO @variable  --This seems wrong...

    PRINT ('Hello, I am # ' + CAST(@variable AS VARCHAR(10)))
END

CLOSE Curse
DEALLOCATE Curse

Isn't the location of the inner FETCH NEXT (inside the WHILE loop) incorrect?  It should be at the end of the loop, or at least after the query (queries) that need to use the variable, no?

YES, it is incorrect.  The inner FETCH NEXT should go at the END of the loop.  The code above would result in the following:

Hello, I am # 2
Hello, I am # 3
Hello, I am # 4
Hello, I am # 5
Hello, I am # 5

Correcting the problem, and putting the FETCH NEXT at the end of the loop, results in the correct & expected output:

Hello, I am # 1
Hello, I am # 2
Hello, I am # 3
Hello, I am # 4
Hello, I am # 5

Now to go submit this to RedGate...


Oracle "insert /*+APPEND*/" into empty table I have following code in script:

execute immediate 'truncate table tab_a drop storage';

insert /*+APPEND*/ into tab_a
select (...)

I am wondering if this APPEND hint can cause any performance change, since table is truncated and storage dropped.
Any info, hints or resources are welcome.

Without knowing far more about what you are doing its impossible to say. However, I can say what APPEND, essentially, does.
It will do a direct-path insert to the table which simply means it will write the data directly to data blocks and will bypass the buffer cache. So generally you will use the hint when loading a large dataset and definitely not for just a handful of records.
There are some things to consider such as serialisation, space re-use, etc. For a very quick list take a look at AskTom https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484 .

It is another option you can consider using depending on the work you are doing.


How to forcibly flush the undo log/tablespace of UNDOTBS1 I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me the UNDOTBS1 tablespace not large enough.

I'm aware of something like CHECKPOINT NUM, which normally allows you to flush the undo logs at the given NUM. I was wondering if there was a similar functionality for anonymous blocks in PL/SQL given that I do not have the rights to modify UNDOTBS1.

    SET serveroutput ON;
    DECLARE
      --Cursor declaration
      CURSOR a_cur IS
      SELECT * FROM aTable
      FOR UPDATE;

      --Cursor row
      a_rec a_cur%ROWTYPE;

    BEGIN
      -- TABLE
     OPEN a_cur;
      LOOP
       FETCH a_cur INTO a_rec;
       EXIT WHEN a_cur INTO %NOTFOUND;

       --Update the record
       UPDATE aTable SET
         field1 = 'aVal',
         field2 = 'aVal',
         field3 = 'aVal'
       WHERE CURRENT OF a_cur;

       -- Attempting to force flush the UNDOTBS1 since it is not large enough
       --COMMIT;

      END LOOP;
      CLOSE a_cur;
      COMMIT;
    END;

Undo data is used to ensure ACID of the database. It supports your transaction and cannot be removed until you committed or rolled it back. Read "What Is Undo?" in Database Administrator's Guide.

You can however commit your transaction to release some undo space, but you lose the ability to roll back your changes.

If the DML you are performing requires more undo space because there are a lot of records updated/deleted/inserted and your undo tablespace is not sized correctly, DML will fail, because Oracle cannot allocate extents to support your transaction.


SQL Server Dynamic Memory management I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's dynamic memory manager.

Can someone confirm my understanding of SQL Server’s dynamic memory management is correct?

In SQL Server 2005/2008/2008R2 x64

a)
Max amount of memory taken by locks is limited to 60% of the buffer pool memory (max server memory). By disabling lock escalation and doing a huge insert I can see that SQL Server can take up to 60% of buffer pool memory before throwing error number 1204 (out of memory for locks)

b) Plan Cache size is limited to

 a) Number of plans in cache -- 160K

 b) Plan Cache upper limit: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB.

Once this threshold is reached SQL server will start evicting execution plans from the cache until SQL server stop seeing internal plan cache pressure

c) Workspace memory is limited to 75% of max server memory and a single query can not take more than 25% of  the (available 75% max workspace memory) unless overridden by resource governor.

My question is

Is there a limit on max memory taken by database page cache before lazy writer start evicting pages out of memory? How does it controlled? By looking at free pages count in the buffer…**

How sql server controls memory taken by "transaction log cache" before flushing log to the disk?

Thanks in advance.

This isn't a complete answer, as I admit I don't know the complete answer, but can't fit into a comment.

Short detour - regarding SQLOS memory there is a wealth of information coming from a guy that worked on the internals of SQL Server storage team - Slava Oks. He has lots of articles on MSDN blogs regarding the SQLOS memory manager and they are all worth a read. Now I know we're on to SQL Server 2012, but the info is valuable nonetheless.

Now, regarding your first question, if there's a limit of max memory taken by db page cache before being cleared out and how is it controlled, I'll quote his word on it.

From article SQLOS's memory manager and SQL Server's Buffer Pool:

  Buffer Pool commits pages on demand. Depending on internal memory requirements and external memory state, it calculates its target, amount of memory it thinks it should commit before it can get into memory pressure. To keep system out of paging target is constantly recalculated. Target memory can't exceed max memory that represents max server memory settings. Even if you set min server memory equal to max server memory Buffer Pool will only commit its memory on demand. You can observe this behavior by monitoring corresponding profiler event.

From this article's comment I understand that SQL Server uses a LRU algorithm (LRU = least recently used). If you really need to go in depth, SQL Server's LRU algorithm in particular is described in the following research paper: The LRU-K Page Replacement Algorithm For Database Disk Buffering.

Now, for your second question, the log caches are not really exposed so they can't be changed/tuned, as they are a part of the SQLOS internal memory structure. Quoting from an answer from MSDN forum:

  Essentially each database has a log buffer/cache for its transaction
  log which is a small contiguous memory allocation, that is 60K in size
  and used by active transactions, and flushed when a transaction
  commits, or when the buffer space fills up.  Multiple active
  transactions can be interleaved in the log buffer, and if any one
  transaction commits, the buffer memory is written to the transaction
  log file by the log writer.  If the space fills up, the contents get
  flushed to disk allowing the buffer/cache memory to be reused.  This
  is why log IO can range for 512bytes to 64K (its actually 60K), but it
  is always sequential so varying write sizes doesn't matter that much.
  The format of the log records is different than data pages as
  documented in the BOL
  (http://msdn.microsoft.com/en-us/library/ms190925.aspx), so the size
  of a log record is variable, but the size of the log buffer/cache is
  consistent.


Investigating specific SELECT queries For a particular row in a table how can I determine the number of reads done to it such as

SELECT * FROM MyTable WHERE ID = <something>

and when and where the read query executed?

I'm investigating a product that was "LOST" in a small company. So I wanted to see if anybody looked it up before it disappeared.

There is nothing native in SQL Server that is going to track that level of information. Based on what version of SQL Server you are using there is SQL Server Audit (SQL Server 2008 or higher) that can track database level access, but it does require Enterprise Edition.

You could also roll out your own method of logging using Profiler or Extended Events that just capture object access for a given database. As long as you are not trying to capture the query and all it should be pretty low overhead, but it would be based on how active your system is.


Is there any tool to convert Oracle commands to MySQL? Is there any tool which I can use to convert Oracle commands to mysql commands?

If you're looking for translating create table statements, there's SQL Fairy


How does the T-SQL optimizer handle "and false" In SQL SERVER 2008 I'm thinking about a query such as:

SELECT ...
FROM ...
WHERE ...
  AND @a=1
UNION
SELECT ...
FROM ...
WHERE ...
  AND @a=2

Given that @a is clearly the same value, and therefore either the first SELECT returns results or the second SELECT does, will the query optimizer identify one of the queries as false and not run it?  Would it be better for me to put them in an IF statement? The actual query is a bit more complicated than written of course, so the if would make it like 4 different queries, which would be a pain to maintain.

I'm hoping the optimizer is smart enough to see the 'and false' and not waste time with it.

With respect to your question

  will the query optimizer identify one of the queries as false and not
  run it?

You'd need to check the execution plans. An example below where it doesn't.

DECLARE @P INT =1

SET STATISTICS IO ON;

SELECT name
FROM sys.objects
WHERE type='P' AND @P=1
UNION
SELECT name
FROM master..spt_values
WHERE number >0 AND @P=0

Output

Table 'Worktable'. Scan count 0, logical reads 0
Table 'sysschobjs'. Scan count 1, logical reads 5

Notice that master..spt_values was not touched in the query execution. The reason for this is that the seek on this table is under a filter with a start up expression predicate of [@P]=(0). As this condition is not met the seek is never actually executed.

Both the Startup Expression and number of executions are shown in the operator tool tips as below.

With regard to the other part of your question

  Would it be better for me to put them in an IF statement?

Yes probably despite that. You're then guaranteed that the behaviour that you get is that which you want. Also even though master..spt_values doesn't have any pages read above it does still have an IS lock taken out against it (which doesn't happen in the IF ... ELSE version)

Better still might be to use an If that calls separate stored procedures for each branch. You haven't shown the rest of your code but one possible issue with these mutually exclusive branches might be parameter sniffing. The batch will be compiled as per the first set of parameters seen. These might be wholly unsuitable for the other branch(es). Splitting into child batches allows each branch to be compiled separately taking into account the parameter values that actually occurred when that branch was executed.


Oracle: converting a TNS entry with SID to instantclient? How do I convert a TNS entry that has a SID to instantclient format?

I'm able to connect with a TNS string but not with the equivalent (I thought!) instantclient string.

$ ./sqlplus 'myname/mypass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.example.com)(PORT=1521))(CONNECT_DATA=(SID=mysid)))'
Connected to: ...

$ ./sqlplus myname/mypass@//myhost.example.com:1521/mysid

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Use your working connection that connects using the SID to find which services are in the database. If you are connected run:

select name, blocked from v$active_services;

and use one of the names you get to connect to.

sqlplus myname/mypass@//myhost.example.com:1521/{a name from query}


Entity Framework SaveChanges fails due to a trigger Trying to implement Entity Framework for an existing database (SQL Server 2012). I have a VB application running and trying to convert it to the EF based Web API.

When I insert data into the table InboundEquipment where InboundEquipmentID is an IDENTITY column. I also have an insert trigger for this table, which uses InboundEquipmentID to insert into another table Message214Status. No FK relationship between these tables.

Database objects:

CREATE TABLE [dbo].[InboundEquipment](
    [InboundEquipmentID] [bigint] IDENTITY(1,1) NOT NULL,
    ...

CREATE TABLE [dbo].[Message214Status](
    [InboundEquipmentID] [bigint] NOT NULL,
    ...

ALTER TRIGGER [dbo].[InboundEquipment] ON [dbo].[InboundEquipment] FOR INSERT AS
DECLARE
    @biInbndEquip_ID    BIGINT,
    @iCust_ID           INT,
    ...
SELECT  @biInbndEquip_ID= InboundEquipmentID,@iCust_ID= c.Cust_ID,  ...
FROM
    INSERTED I
    JOIN sometable c WITH(NOLOCK)ON ...
INSERT INTO dbo.Message214Status (InbndEquip_ID,    Cust_ID) VALUES
    (@biInbndEquip_ID,@iCust_ID )
...

My VB code:

db.InboundEquipment.Add(ibData);
try
{
    db.SaveChanges();
    var IBEquipID = ibData.InboundEquipmentID;
}
catch (Exception ex)
{
    return ResponseMessage(Request.CreateErrorResponse (HttpStatusCode.InternalServerError, "ERROR:" + ex.Message));
}

public partial class InboundEquipment
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long InboundEquipmentID { get; set; }
    ...

DBContext.db.SaveChanges() fails with this exception:

  InnerException {"Cannot insert the value NULL into column 'InboundEquipmentID', table 'dbo.Message214Status'; column does not allow nulls. INSERT fails.
      \r\nThe statement has been terminated."}    System.Exception {System.Data.SqlClient.SqlException}

Basically the insert trigger fails. I cannot change database objects since there is another existing application that depends on this database that cannot be changed.

When I disable the insert trigger,  the save completes successfully.

What could be the reason for the trigger failure?

Most likely there is a problem with the JOIN to sometable that is filtering out any rows from matching, hence the @biInbndEquip_ID variable never gets populated.

HOWEVER, even if you fix that, you still have a bigger problem to deal with: your trigger logic is written to handle a single row. If a multi-row INSERT is done, then the trigger will only ever grab one of the values that was inserted to place into the Message214Status table. Get rid of the @biInbndEquip_ID, @iCust_ID, etc local variables in the trigger, and rewrite it to be a simple INSERT...SELECT. For example:

INSERT INTO dbo.Message214Status (InbndEquip_ID, Cust_ID, ...)
  SELECT ins.[InboundEquipmentID], ins.[CustID], ...
  FROM   INSERTED ins
  INNER JOIN sometable c
          ON c.[join_column] = ins.[join_column];


Creating temporary table dynamically using condition I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional way like the below code.

CREATE PROC test @var1 CHAR(1)
as
BEGIN
IF(@var1 = X)
BEGIN
SELECT * INTO #result
FROM TABLE1
END
IF(@var1 = Y)
BEGIN
SELECT * INTO #result
FROM TABLE2
END
IF(@var1 = Z)
BEGIN
SELECT * INTO #result
FROM TABLE3
END

SELECT * FROM #result r
END

The objective is to finally have a table named #result with columns based on the variable(@var1) value

Edit 1:
Since this is a good candidate for the use of dynamic SQL, as shown below, but I won't be able to use the #result table outside dynamics SQL scope which is what I need.

CREATE PROC test @var1 CHAR(1)
as
BEGIN
-- USING  dynamic sql
DECLARE @sql VARCHAR(MAX)
IF(@var1 = 'X')
BEGIN
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id] INTO #result
FROM sys.tables t'
END
IF(@var1 = 'Y')
BEGIN
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id] INTO #result
FROM sys.tables t'
END
IF(@var1 = 'Z')
BEGIN
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id],t.[parent_object_id] INTO #result
FROM sys.tables t'
END

EXEC (@sql)
SELECT * FROM #result r
END

Supported?

I know this is tagged as 2008R2. Since that's officially out of support, perhaps an upgrade is in your future. If you end up on a version of SQL Server > 2012, you can use code like this:

CREATE OR ALTER PROCEDURE dbo.dynamic_temp ( @TableName NVARCHAR(128))
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #t ( Id INT );
    DECLARE @sql NVARCHAR(MAX) = N'';

    IF @TableName = N'Users'
        BEGIN
            SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Users AS u WHERE u.Reputation > @i';
        END;

    IF @TableName = N'Posts'
        BEGIN
            SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Posts AS p WHERE p.Score > @i';
        END;

    SELECT   column_ordinal, name, system_type_name
    INTO     #dfr
    FROM     sys.dm_exec_describe_first_result_set(@sql, NULL, 0)
    ORDER BY column_ordinal;

    DECLARE @alter NVARCHAR(MAX) = N'ALTER TABLE #t ADD ';

    SET @alter += STUFF((   SELECT   NCHAR(10) + d.name + N' ' + d.system_type_name + N','
                            FROM     #dfr AS d
                            WHERE    d.name <> N'Id'
                            ORDER BY d.column_ordinal
                            FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(4000)'), 1, 1, N'');

    SET @alter = LEFT(@alter, LEN(@alter) - 1);

    EXEC ( @alter );

    INSERT #t
    EXEC sys.sp_executesql @sql, N'@i INT', @i = 10000;

    SELECT *
    FROM   #t;

END;
GO

The idea is to use dm_exec_describe_first_result_set to determine which columns your result produces, along with their datatypes. We can use that to generate a dynamic ALTER TABLE statement to add those columns to a base #temp table created outside the scope of the dynamic SQL.

That makes inserting and selecting data from it pretty easy.

I was blogging about this when I remembered this question. Again, sorry there's nothing as easy for < 2012.


procedure to select the first name of the passed id I have a table person_name

DESC PERSON_NAME
Name          Null     Type
------------- -------- ------------
ID         NOT NULL NUMBER(9)
FIRSTNAME           VARCHAR2(20)
LASTNAME   NOT NULL VARCHAR2(30)

SELECT * FROM PERSON_NAME
id | firstname | lastname
--------------------------
1  | Bruce     | Wayne
2  | Clark     | Kent
3  | Barry     | Allen
4  | Hal       | Jordan

I have the following procedure

create or replace
PROCEDURE SEARCH_PERSON_USING_ID
(
 IN_ID IN NUMBER
)
AS
  vNAME VARCHAR2(30);
BEGIN
  SELECT pn.firstname INTO vName
    FROM person_name pn
    WHERE pn.id = in_id
     AND  rownum < 2 ;

  dbms_output.put_line('NAME = ' || vNAME);

  EXCEPTION
    WHEN  no_data_found THEN
    vName := null;
END;

It compiles with no problem.
But when I run it, I get the following error

set serveroutput on;
EXECUTE SEARCH_PERSON_USING_SISID(1);

Output:

anonymous block completed
NAME = Bruce

Why the
    anonymous block completed

anonymous block completed is not an error, just an informational message. Actually, when you use EXECUTE, you implicitly use an anonymous block. If you want to get rid of that message, you can turn it off by:

set feedback off


Do I need to rebuild indexes after importing a .bacpac into a database? Do I need to rebuild indexes after importing a .bacpac into a database
or does the database takes care automatically?

There is no such magic in SQL Server!

Although, IMO it would be a great if Microsoft provide such options in the future.

So, you have to do rebuilding indexes on your own.

The good starting point is a set of scripts provided by Ola SQL Server Backup, Integrity Check, and Index and Statistics Maintenance.


Is data encrypted by ENCRYPTBYPASSPHRASE() protected by the service master key? In a recent question, What does the Service Master Key protect?, it was suggested that the service master key protects, among other things, data encrypted by ENCRYPTPASSPHRASE(). Is this true?

No, this is not true, and there is an easy proof. On one server, create a database, and store some data using ENCRYPTBYPASSPHRASE():

CREATE DATABASE blat;
GO
USE blat;
GO
CREATE TABLE dbo.mort(floob INT, splunge VARBINARY(64));
GO
INSERT dbo.mort VALUES
  (1, ENCRYPTBYPASSPHRASE(N'kerplunk', N'secret')),
  (2, ENCRYPTBYPASSPHRASE(N'kerplunk', N'hidden'));

Now, backup the database:

BACKUP DATABASE blat TO DISK = 'C:\wherever\blat.bak' WITH INIT;

Now, on a different server in a different domain or wherever you feel comfortable believing that there are no hidden attachments to some service master key, restore the database:

RESTORE DATABASE blat FROM DISK = 'C:\wherever\blat.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'blat' TO 'C:\somepath\blat.mdf',
  MOVE 'blat' TO 'C:\somepath\blat.ldf';

Finally, retrieve the data from the restored copy, and observe that you can still decrypt it:

USE blat;
GO
SELECT floob, prying_eyes = CONVERT(NVARCHAR(4000),
  DECRYPTBYPASSPHRASE(N'kerplunk', splunge))
FROM dbo.mort;

Results should be:

floob   prying_eyes
-----   -----------
1       secret
2       hidden

So, this means that if a user gets your data and knows the pass phrase (e.g. if you've stored it somewhere in the same database), they can decrypt all of the data without ever worrying about keys or anything else. You might infer from this that ENCRYPTBYPASSPHRASE() is not a very safe way to store passwords, unless you never store the pass phrase a user entered, or unless you use other tools and methods on top of this (scope creep).

As a side note, I will confess that I have absolutely no idea how ENCRYPTBYPASSPHRASE() actually works internally, other than the fact that it uses Triple DES (3DES) encryption. It is non-deterministic - so it might use something like NEWID() or RAND() internally - your guess is as good as mine. I do know that if you run the following, you will get a different binary value each time, and if you take any of the individual output values and ran DECRYPTBYPASSPHRASE() against that value, you'd still end up back with the original:

SELECT ENCRYPTBYPASSPHRASE(N'banana', N'turkey');
GO 5

In fact, you can try this on your own system. Here is one of the binary values I generated, and if you reverse the process, you should get turkey too:

SELECT CONVERT(NVARCHAR(4000), DECRYPTBYPASSPHRASE(N'banana',
  0x010000007854E155CEE338D5E34808BA95367D506B97C63FB5114DD4CE687FE457C1B5D5))


Performance of using a non-clustered index as primary key Background

I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER.

The reason for considering UNIQUEIDENTIFIER is because there is a good chance we will want a feature that allows records to be moved to other systems (foreign database), and handling all relationships with GUIDs is going to make that so much easier.

Having done some research, there are plenty of debates on the pros and cons of each, the main one I am concerned with is performance. The outcome of my research is that using a UNIQUEIDENTIFIER as the PK will not be a problem as long as it isn't also used as the clustered index for the table.

Ok, so the obvious solution would be to split them into two separate columns:

TableA

ID: INT, Identity, Clustered Index
PK: UNIQUEIDENTIFIER, Non-clustered Index

Question

My concern here is when I am defining relationships, how will this effect join performance? Take for example, this 'child' table:

TableB

ID: INT, Identity, Clustered Index
PK: UNIQUEIDENTIFIER, Non-clustered Index
FK: UNIQUEIDENTIFIER, Non-clustered Index

Should I be concerned with any performance loss, particularly with joining tables, by using this approach?

For example, using a query such as:

SELECT * FROM TableB JOIN TableA ON TableA.PK = TableB.FK

Basically, is this the best design for supporting a GUID based PK, or is this going to lead to bad performance? Are there any significant disadvantages to using the approach I suggested?

Here is what I have seen based on several years of working with GUIDs as clustered, and nonclustered primary keys...there is no one correct answer here; the big thing really comes down to the access methods that you are going to use to get to the data, and the volume of data that you are going to return.

Yes, you will have the fragmentation problem with GUID clustered indexes that will have to be managed, however, if your access method uses that GUID as the query predicate, and you return multiple columns from that table, you may be better off suffering the fragmentation hit in order to improve the performance and lessen the impact of reading that data back out.

Here's a very basic example that show that there is less work required to get the data out when it's the clustered index, and the predicate used to get at the data:

/* NonClustered PK */
CREATE TABLE #T1
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T1 PRIMARY KEY NONCLUSTERED ( C2 )
    );
CREATE CLUSTERED INDEX T1_C1 ON #T1 (C1);

/* Clustered PK */
CREATE TABLE #T2
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T2 PRIMARY KEY CLUSTERED ( C2 )
    );

/* Insert 10 rows into each table */
INSERT INTO #T1
        ( C1, C2, C3, C4 )
VALUES  ( 0, '58BBB460-1AFA-4177-BA78-798DA19E0C97', 'some text', 'C4 text')
, ( 1, '17E8163B-BE21-44C7-A7B7-4997265A139D', 'some text', 'C4 text')
, ( 2, '16AACAB8-CD77-4A8D-BE87-9E433CD157EC', 'some text', 'C4 text')
, ( 3, '787D0714-F92A-4963-89E5-3F5DBF518EA7', 'some text', 'C4 text')
, ( 4, '5C720476-D4BE-4047-9F73-DBB1B6B75208', 'some text', 'C4 text')
, ( 5, 'D70F81C5-8AFF-4ABE-BA64-8F5C1A1C6A90', 'some text', 'C4 text')
, ( 6, '1473E5DC-6F3E-4164-988C-E36EE7C695BE', 'some text', 'C4 text')
, ( 7, '648AEA46-4B45-41F9-AA9B-7129062391B4', 'some text', 'C4 text')
, ( 8, '49497ECB-774D-482D-8230-218E97FB2EB4', 'some text', 'C4 text')
, ( 9, 'B90504FA-CEBA-4383-A61F-82F33DAB7A0E', 'some text', 'C4 text');

INSERT INTO #T2
        ( C1, C2, C3, C4 )
SELECT C1 ,
       C2 ,
       C3 ,
       C4 FROM #T1;

/* Index seek on T1 - no lookup as we include the clustering key */
SELECT C1, C2 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Index seek AND key lookup on T1 because there are columns not contained in the PK */
SELECT C1, C2, C3 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Stil a clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2, C3 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

The things that you need to take into account, and think about are going to be:

Storage performance
Performance expectations around reads and writes
Access methods (columns returned, and columns used for the predicates)
Space available (bear in mind the clustering key is included in all nonclustered indexes, meaning that you will use more space, and waste more in the cache

Sadly there is no single correct answer for this, you can only get the appropriate expectations, and performing extensive testing to see if those expectations can be met with whatever data model you design, and if not, look to make adjustments to improve them.

  Are there any significant disadvantages to using the approach I suggested?

It is very difficult to say. The only real disadvantage is that, if most of your queries come in via the nonclustered PK, and you are very read heavy you are going to see a great deal of increased IO as you will be performing key lookups on every call.

If your storage can withstand that, then it's great. And on the upside, you don't have the fragmentation problem which can lead to index maintenance hell over time (and can mean that you can't rebuild these things if you are running AGs and don't have maintenance periods).


Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8 I am looking into upgrading some major components of a webserver (php, apache, mysql).

I've done the update to 5.7 from 5.6. I've updated to php7.3.

What I wonder now is: I see that a mysql community edition is available (version 8). After a first install it failed to start so I rolled back to 5.7.
Now I'm wondering if it's worth the trouble migrating to mysql8 or even if I better would update to mariadb. And in the latter: which version of mariadb would I use or which one corresponds with mysql5.7 which I currently have installed.

Is mariadb/mysql8 stable?

For clarity. This is a production server. I'm not looking for new fancy features. But perhaps some more recent versions have better performance.

Thanks a lot in advance!

Any version declared "GA" is "good".

5.7 is only one major version behind what Oracle is providing.  There have been a lot of improvements in 8.0, but, as with any upgrade, you may or may not be affected.

MariaDB 10.3 is a good parallel to 5.7, but there could be differences -- in features and in Optimizations.

It is good that you are eager to upgrade, but since you had troubles with 8.0, I think it is reasonable to stay with 5.7 for a while.  (Some people are struggling with upgrading from 5.5 or even older versions!)


Framework to effectively identify blocking queries I need a routine to effectively identify which queries caused blocking.  This is related to my previous question How to find the query that is still holding a lock?.

I know there is a bunch of material online regarding this, but all of them are based on the premise that the last SQL statement on an active session is most likely the one who acquired the lock (hence generating the blocking), which is not always true (in my case, never).

I've set the blocking-process-threshold to 30 seconds and started analysing the Blocking Process Reports (BPR).
These reports are fired every time a blocking occurs, when the threshold is reached.
It contains information about the blocked spid and the blocking spid.

Often the blocking spid runs a couple of statements after the one that acquired and is holding the lock on a resource (table, page or row): so despite of the report content, I remain clueless about which query exactly caused that block.

Usually the SQL Server DMVs show only the last SQL text for each session_id, and the DMVs related to active locks (such as sys.dm_tran_locks) also don't address this issue.

Tuning the blocked queries is not the best approach here: our application is all based on dynamic SQL embedded on client code, we don't use stored procedures and based on the blockings that I saw until now, all of the blocked queries were correctly indexed and written.

I think an option to solve this would be to collect candidate queries, which could have generated a blocking and then lookup on this info using timestamp and spid gathered on BPR. Do you agree? If so, can you point a way to do this with the least overhead possible using xEvents?

I'd suggest looking for long-running sessions, using an XEvents session.

The problem you're describing sounds like you have client code that is performing row-by-agonizing-row (RBAR) processing instead of using efficient set-based approaches.

Poorly designed client applications may do something like this:

Connect to SQL Server to get a list of items that need processing.  The query result is held open until all rows have been processed.
perform some long-running process on each row.
Close the query.

What should happen is:

Connect to SQL Server
Run a query, caching the results of the query locally, and closing the connection.
Run the long-running process against each row without keeping the original query open, thereby preventing blocking.

A workaround for SQL Server can be implemented using snapshot isolation row versioning.  See this Technet document for details.  Essentially, row-versioning allows writers to not block readers, vastly reducing blocking.


Database normalization, should I combine these two tables? I am sort of new to database normalization and working with a database that I did not create. The database has a table for users, and a separate table for user addresses. Both tables use the user id as their primary key, and every user is required to have an address. Since every user is required to have an address, should the information be added to the user table for proper normalization?

At the theoretical level, it does sound like these two tables can be combined.

At the practical level, unless you're rewriting everything that interacts with the database, no explicit significant harm should arise if you leave it as is.

If the only duplication of data is the user id, then the space to be gained from the change is minor, and probably not worth the cost of modifying your existing code to find the data in its new location.

If we knew more of the specifics of your situation, we might be able to provide a more detailed answer.


Identify column name information from table schema I have multiple tables with with varying names for the same piece of data. For example, day vs business_day. I'd like to identify what column names exist for which table. I think this can be done using schema information, but I'm not familiar with it.
For simplicity, the tables below are 3 separate tables with these column names.
table_1
day    city  weather

table_2
business_day location  status

table_3
day   city  rain

Where day and business_day and city and location are the same piece of information, but unfortunately different naming conventions so I wouldn't be able to use the same selection criteria in a WHERE clause. I'd like to search the table information to see which tables have day or business_day and city or location. The other columns are not important to me in this example. How can I identify table information in this way?

In MySQL and Postgres you can use
MySQL INFORMATION_SCHEMA.COLUMNS
Postgres INFORMATION_SCHEMA.COLUMNS
SELECT COLUMN_NAME, table_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = 'db_name'
  AND column_name LIKE 'day' OR column_name LIKE 'business_day'