Why SQL Server has 900 byte index size limit Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer.

I can think of this way to answer this question.

Page size 8K with usable 8060
900 bytes * 8 = 7200 bytes (900*9 = 8100), so only 8 records can fit in a page at max. If this index is a non clustered, it would also need to store clustered index key which can again be 900 byte long
i.e.
For a single record memory required would be (900 + 900 bytes) i.e. 4 records per page can be stored.

If only 4 records can be stored in a page, IO operations overhead compared to performance  gain would nullify, hence index size limit is 900 bytes.

From Microsoft SQL Server 2008 Internals:

  In fact, improving scalability is the primary reason for the limit to an index key of 900 bytes, or 16 columns, whichever comes first.

At 900 bytes per key, you can have a maximum of 8 index entries per page.  As you decrease the maximum number of index entries per page, you vastly increase the number of levels required to store any given index.  The smaller the key size, the more efficient the index, since it requires fewer pages of data.

As of SQL Server 2016, the maximum byte size for non-clustered indexes has been increased to 1,700 bytes, and 32 columns.


IF EXISTS taking longer than embedded select statement When I run the following code it takes 22.5 minutes and does 106million reads. However, if I run just the inner select statement by itself it only takes 15 seconds and does 264k reads. As a side note, the select query returns no records.

Any idea why the IF EXISTS would make it run so much longer and do so many more reads? I also changed the select statement to do SELECT TOP 1 [dlc].[id] and I killed it after 2 minutes.

As a temporary fix I have changed it to do a count( * ) and assign that value to a variable @cnt. Then it does an IF 0 <> @cnt statement. But I thought EXISTS would be better, because if there were records returned in the select statement it would stop performing the scan/seeks once it found at least one record, whereas the count(*) will complete the full query. What am I missing?

IF EXISTS
   (SELECT [dlc].[ID]
   FROM TableDLC [dlc]
   JOIN TableD [d]
   ON [d].[ID] = [dlc].[ID]
   JOIN TableC [c]
   ON [c].[ID] = [d].[ID2]
   WHERE [c].[Name] <> [dlc].[Name])
BEGIN
   <do something>
END

Any idea why the IF EXISTS would make it run so much longer and do so many more reads? I also changed the select statement to do SELECT TOP 1 [dlc].[id] and I killed it after 2 minutes.

As I explained in my answer to this related question:
How (and why) does TOP impact an execution plan?
Using EXISTS introduces a row goal, where the optimizer produces an execution plan aimed at locating the first row quickly. In doing this, it assumes that the data is uniformly distributed. For example, if statistics show there are 100 expected matches in 100,000 rows, it will assume it will have to read only 1,000 rows to find the first match.
This will result in longer than expected execution times if this assumption turns out to be faulty. For example, if SQL Server chooses an access method (e.g. unordered scan) that happens to locate the first matching value very late on in the search, it could result in an almost complete scan. On the other hand, if a matching row happens to be found amongst the first few rows, performance will be very good. This is the fundamental risk with row goals - inconsistent performance.

As a temporary fix I have changed it to do a count( * ) and assign that value to a variable

It is usually possible to reformulate the query such that a row goal is not assigned. Without the row goal, the query can still terminate when the first matching row is encountered (if written correctly), but the execution plan strategy is likely to be different (and hopefully, more effective). Obviously, count(*) will require reading all rows, so it is not a perfect alternative.
If you are running SQL Server 2008 R2 or later, you can also generally use documented and supported trace flag 4138 to get an execution plan without a row goal. This flag can also be specified using the supported hint OPTION (QUERYTRACEON 4138), though be aware it requires runtime sysadmin permission, unless used with a plan guide.
Unfortunately
None of the above is functional with an IF EXISTS conditional statement. It only applies to regular DML. It will work with the alternate SELECT TOP (1) formulation you tried. That may well be better than using COUNT(*), which has to count all qualified rows, as previously mentioned.
That said, there are any number of ways to express this requirement that will allow you to avoid or control the row goal, while terminating the search early. One last example:
DECLARE @Exists bit;

SELECT @Exists =
    CASE
        WHEN EXISTS
        (
            SELECT [dlc].[ID]
            FROM TableDLC [dlc]
            JOIN TableD [d]
            ON [d].[ID] = [dlc].[ID]
            JOIN TableC [c]
            ON [c].[ID] = [d].[ID2]
            WHERE [c].[Name] <> [dlc].[Name]
        )
        THEN CONVERT(bit, 1)
        ELSE CONVERT(bit, 0)
    END
OPTION (QUERYTRACEON 4138);

IF @Exists = 1
BEGIN
    ...
END;


Manually set log file size after shrink SQL Server 2008 R2 Am becoming a somewhat involuntary DBA at work at themoment and really need some help on something.

We have a 40GB database in Full Recovery Mode, no log backup configured and a huge log file of 84GB.  My plan thus far to salvage this situation is to run a full log backup on the database, shrink the log file and instigate a maintenance plan to run a log backup every night with the database backup to help keep it under control.

My problem is I do not want the log file to shrink down to nothing and spend the first morning on Monday constantly growing.  I have a rough estimate as to what the file should be (about 20% of the database) and would like to set this from the get-go to ensure as much contiguous space as possible.  Is this just a case of changing "Initial Size" under database Properties -> Files?  I would guess as well that the database would need to be offline for this to occur?

Thanks in advance

Your file management can be a completely online operation.  You have two paths, depending on your need to retain your log information for recovery purposes:

Point in time recovery not needed

Convert the database to SIMPLE recovery.  Execute a checkpoint to write transactions to disk.
Flatten the log.
Resize the log to the appropriate size.

I also recommend setting a fixed growth amount and unlimited growth (so as to help manage your log better).  Note, fixed growth amount is very much an it depends amount, I'd recommend going with 1-2 GB initially depending on how much growth that log could expect to see.  Ideally, your log won't grow much, so this shouldn't have much of an impact.  If your log is growing regularly, you might need to revisit your size.

Accomplished using:

ALTER DATABASE [foo]
SET RECOVERY SIMPLE;

CHECKPOINT;

DBCC SHRINKFILE (foo_log,0);

ALTER DATABASE [foo]
MODIFY FILE (NAME=foo_log,SIZE=8000MB,MAXSIZE=UNLIMITED,FILEGROWTH=1000MB);

--Optional if you want the database in full recovery mode
--for point in time recovery going forward
ALTER DATABASE [foo]
SET RECOVERY FULL;

Point in time recovery needed

The biggest hangup will be that you can not shrink your log file past your currently active VLF segment.  To see this, you can use DBCC LOGINFO in the database context.  Any segment with a Status=2 is active.  To clear active segments, you will need to run a transaction log backup when no transactions are currently active in that segment.  Your steps are:

Run a transaction log backup.
Shrink your file. (Ideally flatten, but if your database is active this will be hard to do).
Repeat steps 1 and 2 until your log is an appropriate size, ideally as small as possible.
Resize the log to the appropriate size.

Accomplished using:

BACKUP LOG [foo] TO DISK='<Location of t-log backup>';

DBCC SHRINKFILE (foo_log,0);

--Repeat the above until your log file is small "enough"

ALTER DATABASE [foo]
MODIFY FILE (NAME=foo_log,SIZE=8000MB,MAXSIZE=UNLIMITED,FILEGROWTH=1000MB);

Some additional resources to understand what's going on here:

SQL Server Recovery Models
Kimberly Tripp's t-log recommendations
Database Checkpoints


Difference Between Row Level and Page Level Locking and Consequences When attempting to run my Maintenance Plan, I receive the following error:

  Executing the query "" failed with the following error: "The
  index "" (partition 1) on table "" cannot be reorganized
  because page level locking is disabled."

We currently have Row Level locking enabled on this Index. I can enable Page Level locking, but I am unsure what the repercussions are.

My Question Is: What is the difference between the two locking schemes, and what are their real-world (in production) consequences?

Executing the query "" failed with the following error: "The index ""
  (partition 1) on table "" cannot be reorganized because page level
  locking is disabled."

The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not possible if page locks have been disabled. The only way to defragment without page locks is to lock the entire partition, which is not possible for REORGANIZE as its online only.

  What is the difference between the two locking schemes, and what are
  their real-world (in production) consequences?

You need to grasp what a record and page are to evaluate the impact of disallowing a particular lock type. If you are unfamiliar with SQL Server storage internals, start with Anatomy of a Record and Anatomy of a Page. Put very simply:

rows = records
rows are stored in pages of 8kb

If you were to alter the permitted lock types:

Disable page locks = Row and table locks only
Disable row locks = Page and table locks only
Disable both = Table locks only

There are two scenarios I'm aware of where it can be beneficial to disallow a lock type. Doesn't mean there aren't others, hopefully someone else will step in with examples.

A frequently accessed lookup table, that changes infrequently - By disabling both page and row level locks, all readers will take a shared table lock. This is faster/cheaper rather than the usual intent-shared on the table, followed by intent-shared on a page and finally a shared lock on a specific row or rows.

Preventing a specific deadlock scenario - If you encounter deadlocks caused by concurrent processes acquiring locks that are frequently on the same page, disallowing row locks results in page locks being taken instead. Only one process can then access the page at a time, the other must wait.

The first example is micro-optimisation and unlikely to yield measurable benefit on a typical system. The second will solve that particular deadlock scenario but may introduce unexpected side effects e.g. killing concurrency in a different section of code. Difficult to assess the impact fully, approach with caution!

The default is for both to be enabled and this should not be changed without good cause.


Rapidly increasing TempDB write latency We have two servers that were set very similarly, but not identically. These two servers have hugely different performance running the same application.

On server A the application runs successfully (although slowly).

On server B the application runs successfully (and a bit faster) initially, but soon grinds to a halt with SQL timeout issues.

After some investigation I found that the write latency to the temp DB on Server B was 14s (not a typo) (using the query from here: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/).
I thought that this might be down to the fact that the temp DB was on the C: drive on Server B (and the swap file was being used). So I have moved the temp DB to the D: drive (where the other database files live). After restarting SQL server the write latency was intially around 1s - still terrible from what I have read, but the application was running happily in that state so it would be good enough for now. Less than 24 hours later the write latency on the temp DB has climbed to 11.5s and we're seeing timeouts again.
The temp DB write latency on server A is under 10ms.

So my question is, what could be causing this rather dramatic difference and change in performance?

My SQL is rather rusty so please don't assume that something is too obvious to be the solution!

The main differences that I know of between the two servers are:

Server A has two disks in a RAID mirror, the mirror is then split into two partitions, whilst Server B has two disks not in any kind of RAID set up
Server B has a couple of extra databases on it, although I don't believe that they are used (PerfStats, ReportServer, ReportServerTempDB).
Server B has eight tempDB files, whilst Server A only has a single file
Server B is running on a slightly stripped down database that has been reduced to a single big customer's data. Server A is processing this customer and a number of others (although the other customers take up a tiny fraction of the processing power needed for the big customer)
The database on Server B was stripped down by a SQL specialist who may well have changed other things that I'm not aware of :( (e.g. they are probably responsible for the 8 temp DB files)

Also in case it helps, there is another desktop machine also running the application as a test. This machine appears to have a single drive split into a couple of partitions, half the RAM of server A and server B but a much more modern CPU. It is performing rather faster than either server and has a tempDB write latency of around 0.5s. Again I think this indicates that we don't need great write latency, just not so terrible that it causes timeouts.

Looks like the disk subsystem on Server B is performing worse than on Server A but, I used to see disk issues not entirely related to disk specs. You could collect some other performance counters such as physical disk --> avg disk sec write (> 25 ms very slow), memory --> page file usage (> 70% bad), cpu --> processor queue length (> 12 very bad), memory --> pages/sec(> 600 slow, > 2500 very slow disk subsystem), sql server: buffer manager --> page life expectancy (< 300 memory issue).

You can also limit the growth of 6 tempdb data files to the size they have now. See what happens.

You should check the waitstats as well on both servers. If you see a lot of PAGELATCH_XX then you will know where to dig more. Jonathan Kehayias has a good article on this theme. And Paul Randal has also a lot of analysis which could help.


Change A Database Name When Restoring I'm going to be moving a database from one server to another. Right now the name makes no sense and I'd like to change it. I found a answer here that makes me think it might be relatively simple. My question is three-fold.

1: Is there a reason I shouldn't rename the database?
2: Would the renaming be as simple as

RESTORE DATABASE [NewDatabaseName]
FROM  DISK = N'C:\OldDatabaseName.bak'
WITH  FILE = 1,
   MOVE N'OldDatabaseName' TO N'C:\NewDatabaseName.mdf',
   MOVE N'OldDatabaseName' TO N'C:\NewDatabaseName_1.ldf',
   NOUNLOAD,
   STATS = 10
GO

3: What's this "NOUNLOAD" parameter? I can't find any reference to it in the searching I've done.

Only if you have applications, T-SQL code, synonyms etc. that reference this database by name. If this is not a production environment, you could test this on the existing copy using ALTER DATABASE ... MODIFY NAME and seeing what breaks, or just taking the current copy of the database OFFLINE to see what kind of things might be depending on it. If this is production, you better take the old copy of the database offline as soon as you restore the copy as a new name, because your apps etc. could still be connecting to the old database and you wouldn't know.
Close, but I think that the second MOVE command should be MOVE N'OldDatabaseName_log'. Of course this will leave the files named with the old database name; you can do more cleanup to modify those files individually if you want it to be squeaky clean (I think this can be overkill except in cases where I have built automation around the assumption that files are named for their container).
NOUNLOAD is not really relevant today, and you can probably leave it out of all RESTORE statements going forward. From the old SQL Server 2000 documentation (the last time it appeared fully documented, IIRC):

  Specifies that the tape is not unloaded automatically from the tape drive after a RESTORE. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices. If a non-tape device is being used for RESTORE, this option is ignored.


moving large PostgreSQL/PostGIS database I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1).

The upgrade process is well-documented. The problem is I don't have enough space on server1 to dump the file there, checksum it, then copy it to server2 and verify sums. I tried:

Piping the dump from server1 to server2 using nc.
Writing a dump file directly to a server2 filesystem which is mounted on server1 using sshfs.

Both times the dump file appears to have been corrupted. pg_restore broke in different places with errors like this:

pg_restore: [compress_io] could not uncompress data: incorrect data check

Can anyone suggest a better way to get this move and upgrade done?

UPDATE: Tried NFS (and gave SSHFS another try). It's clear that these remote filesystems can't reliably transfer this much data. Blocks are visibly missing from the resulting SQL file, causing syntax errors like this during import:

ERROR:  invalid input syntax for integer: "8266UPDATE spatial_ref_sys o set auth_name = n.auth_name, auth_srid = n.auth_srid, srtext = n.srtext, proj4text = n.proj4text FROM _pgis_restore_spatial_ref_sys n WHERE o.srid = n.srid;"

I would recommend dumping the 9.1 database from your new 9.3 server like this:

pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump

I recommend using the 9.3 pg_dump as pg_dump is always backwards compatible, but not forward compatible. In other words, the newer pg_dump will take care of any syntax changes that the new server requires that the older utility doesn't know about.

Be sure to make sure your pg_hba.conf and listen_addresses in postgresql.conf are set up to allow you to remotely connect and dump appropriately as well.

If you wanted to try a dump and restore in one step, you could try this as well:

pg_dump -h remotehost -U remoteuser remotedbname | psql -U localuser localdbname

Hope that helps. =)


PostgreSQL what happens if TimeZone is not set? From the docs on 9.1, I see

  If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if not specified.)

This section was however removed subsequently in 9.2, what happens if 9.2+ is not specified in postgresql.conf or as a server command-line option?

My timezone currently says localtime and I'm wondering what exactly that means and how it's decided. The original text seems to answer it if it's still applicable. Are we still relying on the C library function localtime()?

# show timezone;
 TimeZone
-----------
 localtime
(1 row)

I was trying to add this information in this answer for completeness when I realized that the older docs had more details.

It seems like PostgreSQL 10 still

Looks at the environmental variable TZ first
Proceeds to use the localtime library function.
Looks under the shared directory or the system directory for matching timezone information.

So it seems like the 9.1 docs are still accurate as of PostgreSQL 10.

Internally, you can see the list of timezone information by using SELECT * FROM pg_timezone_names(); (here in the source) which calls pg_tzenumerate_start which the reads the shared directory or system directory above.


Help defining a chained one-to-many relationship tables Sorry if the title is confusing but I couldn't think of a way to properly word this question without laying out what I am trying to do.

Basically I have 3 tables and I'm trying to figure out how to best relate them all given the constraint that each Template should have a region but necessarily doesn't have to country.

 Template
  id
  name
  description
  country_id

 Country
  id
  name
  region_id

 Region
  id
  name

Two solutions that I've come up with but am not really satisfied with are:

add region_id column to template table.  The problem I have with this solution is creating the circular relationship between the region_id in country and in template. Enforcing that region_id is the same in country and template tables also seems difficult to enforce.
add region specific country records to the country table that have no real country value but have a relationship to an existing region.  For example:

Country:
 id:5
 name: North America
 region: 1 -> North America

Solution 2 seems the simplest but I also feel like its not the most elegant solution because I am creating these "fake" country records.

Since Template must be related to a Region, you need a foreign key from Template.region_id to Region.id

Template.region_id will be NOT NULLable.

Template.country_id will be NULLable.

Make a unique constraint on the composite key (Country.id, Country.region_id)

Then you can create a foreign key from (Template.country_id , Template.region_id) to (Country.id, Country.region_id)

This ensures that if the Template is related to a Country, then the Template.region_id is the same as the related Country.region_id

Luckily (for you) when a row's composite foreign key contains one or more null values, then the row is "valid" regardless of the existence of rows in the referenced table.  So having a NULL Template.country_id and a NOT NULL Template.region_id will be fine.

One interesting side effect of this "doubly constrained" system is that it is difficult to update the region_id column of a Country.  Any attempt to update Country.region_id will cause a constraint violation if there are any Templates that contain references to that Country, since the (Template.country_id , Template.region_id) pair will not refer to an existing (Country.id, Country.region_id).

If you must allow this type of update, then you can use an ON UPDATE CASCADE clause when creating the (Template.country_id , Template.region_id) foreign key.


db schema for a object with multiple foreign key tables I have a question table which contains question_id, question_text and choice_type_id ( say freetext, binary choice and multiple choices )

Now, the question table (which has question_id, question_text, choice_type_id) has a foreign key to the choice type table ( choice_type_id, choice_type_desc) . Now all the different choice types have a different schema.

For multiple choice,  I want to store multiple choice fields against a question. For binary choice , I want to store two choices against a question and for freetext, I have no choices.

How do I populate a question's choices based on its type and model this in a sql like db. I would further want to see user interactions with each of these questions and choices within that question ( i.e. the user chose which choices or entered what text for the given question)

I can model the question_choice table as a big table which can have all the three types and based on the type certain columns can have values and all other remain null.

This would be a very sparse representation and I'm not sure how this will scale.

What would be the best schema design considering I want fast reads and the question types will keep increasing further.

Simply include in your model a choice table for each type of choice that your
Your field choice_type_id is essentially a kind of partitioning attribute.  It indicates what type of child table is applicable.

So far you have three tables: MULTIPLE_CHOICE, BINARY_CHOICE, and FREE_TEXT (although you might consider binary choice to be a special case of multiple choice).

Each of these tables is a child of your QUESTION table, i.e. they all have a question_id foreign key.  The multiple choice table will have multiple records pointing to QUESTION whereas the other tables will have one - depending on how you model binary choices.

Note that this physical arrangement could be flattened out using a view to look like the one big table with sparse columns that you want to avoid.  The one big flat (sparse) table might be handy for reporting, but since you're worried about scale you might want to avoid a sparse table for physical storage.

Your user answer tables would be children of the various choice tables that intersect the USER and the appropriate choice, since each question is answered by multiple users.

For more about this approach, see my answers to this question and this question here on DBA.SE.


How does Postgres know which logs have been archived? I'm asking this because Postgres was hard-stopped yesterday, and I fear that there could be partial / corrupt data in one of my archived log segments. I'd like to simply delete the logs from my slave server and just have Postgres start again from the beginning (there are only about 90 segments, so it won't take long), but I'm not sure what to do, to get Postgres to do this.

On master there is archive_status directory in pg_xlog which contains files that mark status of archiving of xlogs.


How can I sync databases to a secondary server for read-only usage? I want to sync databases to a secondary server that will be used read-only for SQL Server Reporting Services.  These syncs can be done once or twice nightly and mid morning;
synced database sizes would be 400 Gb, 50 GB, and 60 GB.

What are my options to approach this scenario?

I planned to implement log shipping using RedGate's product so my backup would be compressed and easier to implement, however I am not sure how to maintain the logfile size for maintenance purposes.  For instance, when do I shrink the logfiles since they may grow enormous until I take a log backup during the night?

Are there any other options like mirroring or replication?

If you have SQL Server 2012 Enterprise Edition you could implement Always On Availability groups.

This is an extension of mirroring (built on-top of windows clustering) which allows the mirrored database to be a readable secondary. The benefit of this is that the secondary is updated as the primary database is updated. However it is expensive as the secondary server does need a full licence.

You can implement log shipping and have the secondary databases in STANDBY mode, which would make them readable. You can then control when the logs are applied. I would recommend a short interval between log backups, this should prevent your logs from expanding so that you do not have to shrink them.


Where to start learning the IDEF1X method of model notation? I recently started learning about data modeling.
After seeing @MDCCL's diagrams and thoughtful approach here, I'm interested in learning more about IDEF1X. I don't want to pay $200+ for the ISO standard.
So where can I begin to learn this appraoch?

Answer left in comments by bbaird:
IDEF1X is commonly described as a methodology, and perhaps the reason we see it as "another way to represent a model" is that it was among the first and so all other graphical representations that have come since have borrowed from it in one way or another (typically through trying to copy features from ERWin).  I've found this to be a good primer on the subject:
http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf
You can also read the original standard here:
https://nvlpubs.nist.gov/nistpubs/Legacy/FIPS/fipspub184.pdf


Script to backup TDE certificate and Key throws error Cannot write into file I am attempting to automate TDE key backups.  I created the stored procedure below and was going to schedule it to be executed as a sql agent job.
However, when I execute the procedure I get this error after about 20 seconds:

Msg 15240, Level 16, State 1, Line 3
Cannot write into file '\\FileServerName\TDEcertificateBackups\ServerName_CertificateName_CurrentDate_key.pvk'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

I have tried the following:

Verified that the SQL Agent account has full access to the UNC path.
UNC to the UNC path to validate it.
File does not already exist as the folder location is empty
Used the PRINT @SqlCommand results and ran them under my domain admin account.
googling it :)

After reading the documentation, saving to a UNC path should work.
Is this a permissions issue that I need to track down with the network/sys admins, an error with my script, or some other "gotcha" I'm not currently aware of?
Stored Procedure:
CREATE OR ALTER PROC dbo.BackupTDEcertificate
AS
SET XACT_ABORT, NOCOUNT ON
/*
    This script can be used to backup the certificate and private key used for TDE.
*/

/*Declare variables*/
DECLARE @DateTime NVARCHAR(8)
SET @DateTime =
        CAST(YEAR(GETDATE()) AS NVARCHAR(4))
        + CAST(MONTH(GETDATE()) AS NVARCHAR(2))
        + CAST(DAY(GETDATE()) AS NVARCHAR(2))
--SELECT @DateTime
DECLARE @CertificateName NVARCHAR(50) = (
    SELECT TOP 1 CER.name [Certificate]
    FROM
        master.sys.certificates CER
    ORDER BY
        CER.start_date DESC
)--SELECT @CertificateName
DECLARE @Path NVARCHAR(150) = '\\FileServerName\TDEcertificateBackups\'
--SELECT @Path

/* Perform the backup*/
DECLARE @SqlCommand NVARCHAR(2000) =N'
        USE master;
        BACKUP CERTIFICATE ' + @CertificateName + '
         TO FILE = N''' + @path + @@SERVERNAME + '_' + @CertificateName + '_' +@DateTime + '.cer''
             WITH PRIVATE KEY (
             FILE = N''' + @Path + @@SERVERNAME + '_' + @CertificateName + '_' + @DateTime + '_key.pvk'',
            ENCRYPTION BY PASSWORD = ''SuperSercretPassword''
            );
'
PRINT @sqlcommand --Actual procedure has this line commented out.
--exec sp_executesql @sqlcommand --Actual Procedure does not comment this line out.

When SQL Server saves certs to disk, it alters the ACLs on the exported files - you're probably running into a permissions issue where the SQL Server Agent account cannot overwrite the file once it gets generated the first time.
See my blog post at sqlserverscience.com for details about file system security.
The key part from that post is:

After those commands have been used to export each key or certificate, SQL Server modifies the Access Control List (ACL) on each file1. This ensure no-one other than the following Windows principals have access to the file:

OWNER_RIGHTS has Full Control. OWNER_RIGHTS is a well-known security identifier, S-1-3-4 that represents the current owner of the object. When an Access Control Entry that carries this SID is applied to an object, the system ignores the implicit READ_CONTROL and WRITE_DAC permissions for the object owner.
Members of the local “Administrators” group have Full Control
The Virtual Account or Managed Service Account used to run SQL Server has Full Control. Note this is not the SQL Server Service Account you configure in SQL Server Configuration Manager. For a default SQL Server instance, the account is typically NT SERVICE\MSSQLSERVER. For a named instance, the account is named NT SERVICE\MSSQL$INSTANCE_NAME.
Inheritance is removed from the object’s ACL to prevent access by any other principal.

You also need to ensure the SQL Server service accounts have share-level permissions that enable writes.


Is the concept of a clustered index in a DB design sensical when using SSDs? When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made explicitly to be deployed on SSD platforms?

http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx
"A clustered index determines the physical order of data in a table."

On a physical disk platform, the design to consider them makes sense to me as a physical scan of the data to retrieve "sequential" rows can be more performant than a seek through the table.
On an SSD platform, all data read access uses an identical seek. There is no concept of "physical order" and data reads are not "sequential" in the sense that bits are stored on the same piece of silicon.

So, in the process of designining an application database is the clustered index consideration relevant to this platform?

My initial thought is that it is not because the idea of "ordered data" doesn't apply to SSDs storage and seek/retreival optimization.

EDIT: I know the SQL Server will create one, I'm just philosophizing about whether it makes sense to think about it during design/optimization.

Ask yourself another question: If the entire database is in memory and I never have to touch the disk, do I want to store my data in an ordered B-tree or do I want to store my data in an unordered heap?

The answer to this question will depend on your access pattern. On most cases your access requires single row look-up (ie. seeks) and range scans. These access patterns require a B-Tree, otherwise they are inefficient. Some other access patterns, common in DW and OLAP, are always doing aggregates over the entire table end-to-end always and they do no benefit from range scans. As you drill further other requirements come to light, like the speed of insert and allocation into a heap vs.  B-Tree may play a role for huge ETL transfer jobs. But most times the answer really boils down to one question: do you seek or range-scan? The overwhelming number of times the answer is YES. And therefore the overwhelming number of times the design requires a clustered index.

In other words: just because is cheap to read it from disk in random order does not imply that you can trash your TLBs and L2 lines in a 64Gb RAM scan bonanza...


What are the dangers of not gracefully shutting down mysql server What are the dangers of not shutting down a windows MySql server gracefully (i.e. hitting the reset switch on the server as apposed to shutting the OS down) ?

It's possible that a transaction may be partially committed leading to unresolvable corruption on reboot. This is especially the case if you use MyISAM tables - InnoDB tables have a double write buffer which is a form of Write Ahead Log (WAL).

A good discussion of the concepts is available here and here. Note that from the Percona article we have "the changes made by transactions will occur in volatile memory and later be flushed to disk". Switch off the machine and your RAM evaporates.

Note that despite these elaborate precautions and systems, many questions here concern MySQL corruption (even with InnoDB engine tables) which shows that no system is perfect. The "horse's mouth" guide to InnoDB (i.e. Oracle/MySQL's own technical article on the issue) is available here.

This corruption can lead to the need for a restore from backup which means that data changed/inserted between then and the power interruption is lost. It's not a good idea on production servers and is the reason that we have UPSs (Uninterruptable Power Supply) on them.


Select Count of one table into another I have one SQL statement as:

SELECT   ARTICLES.NEWS_ARTCL_ID, ARTICLES.NEWS_ARTCL_TTL_DES,
         ARTICLES.NEWS_ARTCL_CNTNT_T, ARTICLES.NEWS_ARTCL_PUB_DT,
         ARTICLES.NEWS_ARTCL_AUTH_NM, ARTICLES.NEWS_ARTCL_URL,
         ARTICLES.MEDIA_URL, ARTICLES.ARTCL_SRC_ID, SOURCES.ARTCL_SRC_NM,
         MEDIA.MEDIA_TYPE_DESCRIP
FROM     RSKLMOBILEB2E.NEWS_ARTICLE ARTICLES,
         RSKLMOBILEB2E.MEDIA_TYPE MEDIA,
         RSKLMOBILEB2E.ARTICLE_SOURCE SOURCES
WHERE    ARTICLES.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF
AND      ARTICLES.ARTCL_SRC_ID = SOURCES.ARTCL_SRC_ID
AND      ARTICLES.ARTCL_SRC_ID = 1
ORDER BY ARTICLES.NEWS_ARTCL_PUB_DT;

Now I need to combine another SQL statement into one which is:

SELECT   COUNT(*)
FROM     RSKLMOBILEB2E.NEWS_LIKES LIKES,
         RSKLMOBILEB2E.NEWS_ARTICLE ARTICLES
WHERE    LIKES.NEWS_ARTCL_ID = ARTICLES.NEWS_ARTCL_ID;

Not sure how I can achieve this as I am new to Oracle. Basically I have one table which contains articles and I need to include the user likes which is in another table.

Any help regarding this is much appreciated.

Try the following:

SELECT   ART.NEWS_ARTCL_ID, ART.NEWS_ARTCL_TTL_DES,
         ART.NEWS_ARTCL_CNTNT_T, ART.NEWS_ARTCL_PUB_DT,
         ART.NEWS_ARTCL_AUTH_NM, ART.NEWS_ARTCL_URL,
         ART.MEDIA_URL, ART.ARTCL_SRC_ID, SRC.ARTCL_SRC_NM,
         MED.MEDIA_TYPE_DESCRIP, LIK.TOTAL
FROM     RSKLMOBILEB2E.NEWS_ARTICLE ART,
         RSKLMOBILEB2E.MEDIA_TYPE MED,
         RSKLMOBILEB2E.ARTICLE_SOURCE SRC,
         (SELECT   AR1.NEWS_ARTCL_ID, COUNT(*) TOTAL
          FROM     RSKLMOBILEB2E.NEWS_ARTICLE AR1,
                   RSKLMOBILEB2E.NEWS_LIKES LK1
          WHERE    AR1.NEWS_ARTCL_ID      = LK1.NEWS_ARTCL_ID) LIK
WHERE    ART.MEDIA_TYPE_IDENTIF = MED.MEDIA_TYPE_IDENTIF
AND      ART.ARTCL_SRC_ID       = SRC.ARTCL_SRC_ID
AND      ART.ARTCL_SRC_ID       = 1
AND      ART.NEWS_ARTCL_ID      = LIK.NEWS_ARTCL_ID
ORDER BY ART.NEWS_ARTCL_PUB_DT;


verify that mongos server is connected to config servers I've been writing a backup script for sharded replica-sets and it's almost done. Except I can't seem to get it to successfully start the balancer backup after everything's all said and done.
Here's the command I'm trying to use to start the balancer back up; keep in mind that this is being run on the actual mongos server via SSH.
sudo -s
mongo -u username -p password --authenticationDatabase db
use config
sh.setBalancerState(true)
exit
exit
exit

I keep getting the following error whenever the scripts hits the startBalancer function, which runs the above code.
SyncClusterConnection::udpate prepare failed:  mongo-conf-0.foo.bar.com:27019:10276
DBClientBase::findN: transport error: mongo-conf-0.foo.bar.com:27019
ns: admin.$cmd query: { resetError: 1 }

I've tried checking against the exit status of the mongo shell process, using something like
if (code != 0) {
  return next('repeat');
} else {
  return next();
}

but regardless of what actually occurs in the mongo-shell, the exit code seems to always be 0.
Any ideas on how I can verify that the mongos process is actually connected to all three configs before I try to re-enable the balancer? I assume the problem is that the mongos server tries to connect to the config server before the mongod process had a chance to finish starting up (part of the backup process for sharded replica-sets is shutting down one of the config servers)

Have you tried using the sh.startBalancer() helper instead?

Rather than a straight update, it does takes an timeout argument as how long to wait for balancing to start as well as a sleep interval in terms of how long to sleep between waiting.  Here's the code from the shell by way of explanation:

mongos> sh.startBalancer
function ( timeout, interval ) {
    sh.setBalancerState( true )
    sh.waitForBalancer( true, timeout, interval )
}

So, you could even break it up and use the waitForBalancer helper if you wished.  For reference, here is the equivalent stopBalancer command erroring out when I tried to stop it with a config server down:

mongos> sh.stopBalancer(2000, 100)
Waiting for active hosts...
Waiting for active host adamc-mbp.local:30999 to recognize new settings... (ping : Tue Dec 31 2013 19:51:32 GMT+0000 (GMT))
Waiting for the balancer lock...
Waiting again for active hosts after balancer is off...
Tue Dec 31 19:51:39.243 error: {
    "$err" : "error creating initial database config information :: caused by :: SyncClusterConnection::udpate prepare failed:  localhost:29000:9001 socket exception [FAILED_STATE] server [localhost:29000] ",
    "code" : 8005
} at src/mongo/shell/query.js:128


Trying to make sense of the InnoDB log file stats I've been reading the high performance mysql book by Baron Schwartz and after reading about how important it is to set the log file size correctly, I thought I'd take a look at one of our servers at work.

I am trying to improve server performance by fixing the bad configuration from a former employee but I am new to this and I thought I'd ask. Server has around 37000 tables in total.

I am not sure if I should change log file settings or leave it alone. I do see write speed spikes in the server. Write speed almost never cruises in a range. It will do (as an example) 20mb at one point, 400kb the next, 40mb the next, 1mb and so on.

Any suggestions are welcome. Thank you for looking.

Show Engine INNODB STATUS

---
LOG
---
Log sequence number 18671411503063
Log flushed up to   18671411503063
Pages flushed up to 18671411503063
Last checkpoint at  18671411502723
Max checkpoint age    3478212404
Checkpoint age target 3369518267
Modified age          0
Checkpoint age        340
0 pending log writes, 0 pending chkp writes
53615 log i/o's done, 0.56 log i/o's/second

my.cnf

innodb_log_buffer_size = 8M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2

37000 tables, itself, is really bad.  Each table is implemented as 1 to 3 files on disk.  This leads to a burden on the OS.  Also, there are various caches (controlled by multiple settings, such as table_open_cache) that could overflow, and probably run inefficiently if made large.

0.56 log i/o's/second sounds trivial.  A spinning drive can (Rule of Thumb) do 10 IOPs.  So the log I/O is only 5% of the available bandwidth.  (Better on SSDs.)

It is very rare that a performance problem is traced to a poorly sized log file (either too large or too small).  (Or maybe I am too dumb in this area?)

Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size
I recommend 0.2 to 2.5.  It means "Minutes between InnoDB log rotations".
Innodb_log_waits / Innodb_log_writes
I recommend 0 to 0.1%.  If out of range, Increase innodb_log_buffer_size.
innodb_log_files_in_group should be 2.  Larger values are possible but they have been tentatively linked to bad performance.

If you would like further tuning and performance critique, see this .

Analysis of VARIABLES and GLOBAL STATUS

The Important Issues

Apropros the original question:  ( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 144 minutes.  This is reasonably close to the optimal of 1 hour so that I suggest that your log settings are fine.
(One must stray quite far from the optimal to get into trouble.)

innodb_buffer_pool_size = 87G is probably OK for 126G of RAM, despite what the analysis below says.

innodb_log_buffer_size -- increase from 8M to, say, 100M.
This may be one of the more important recommendations, but I can't say how important.

Your code seems to be deleting almost as fast as it inserts; what is going on?
In particular, using MySQL as a "queue" has various problems.

Innodb_row_lock_time_avg = 23s -- This is extremely high, and it worries me, but I don't know what to recommend.

Max_used_connections = 158 while max_connections = 1000.
I recommend lowering `max_connections to 500 as a way of preventing a burst of connections from blowing out RAM.

What framework are you using?  It is doing some things, such as SHOW CREATE TABLE and SHOW VARIABLES, that unnecessarily burden the processing.

Suggest increasing bulk_insert_buffer_size from 8M to 32M since you have so much RAM.

Here comes another "more important" recommendation:
161 tmp tables created per second!  Let's look at some of the slower queries.
By adding composite indexes and/or reformulating the queries, performance
may be significantly improved.
See my comments on using the Slowlog to identify such queries in http://mysql.rjweb.org/doc.php/mysql_analysis .
(If you like, start a new Q&A to discuss one of the queries.)

Currently max_tmp_tables = 32 and tmp_table_size = 1G.  It might be better to have max_tmp_tables = 100 and tmp_table_size = 100M.  (Even better would be to work on avoiding some of the tmp tables.)

Of those tmp tables, 1.1/sec are on disk.  This is in spite of the huge values for tmp_table_size and max_heap_table_size of 1GB each.  (Becuase you have so much RAM, I do not have a good argument for lowering those values.)

8.8% of SELECTs are "full joins".  And 146 full table scans/sec.  This may or may not be significant.  I'll wait until such shows up in the slowlog.  When a table has just a few rows; the alarming metrics don't matter.  When a table has lots of rows, they beg for better indexing, etc.

Do you use Cursors in your Stored Routines?

(Some STATUS values confirm that table_open_cache = 20000 is probably OK.)

Be aware that init_connect = SET NAMES utf8 is ignore when connecting as root (or other SUPER user).

innodb_large_prefix is removed (as obsolete) in 5.7.7.  You should remove it when you upgrade.

Why are you running with tx_isolation = READ-COMMITTED?

Details and other observations

mysqld has been running 15 days.  Percona 5.6.32.  126GB of RAM.

( table_open_cache ) = 20,000 -- Number of table descriptors to cache
-- Several hundred is usually good.  (We have alredy discussed this.)

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 87040M / 16 = 5440MB -- Size of each buffer_pool instance.
-- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_max_dirty_pages_pct ) = 60 -- When buffer_pool starts flushing to disk
-- Are you experimenting?

( Innodb_os_log_written ) = 324,963,533,824 / 1314928 = 247134 /sec -- This is an indicator of how busy InnoDB is.
-- Very busy InnoDB.

( Innodb_log_waits / Innodb_log_writes ) = 15,857 / 216746 = 7.3% -- Frequency of needing to wait to write to log
-- Increase innodb_log_buffer_size.

( Innodb_rows_deleted / Innodb_rows_inserted ) = 116,716,394 / 143158563 = 0.815 -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( Innodb_row_lock_time_avg ) = 23,465 -- Avg time to lock a row (millisec)

( max_tmp_tables * tmp_table_size / _ram ) = 32 * 1024M / 129024M = 25.4% -- Pct of RAM potentially consumed by tmp tables
-- Swapping is bad; decrease max_tmp_tables and/or tmp_table_size. Or lower innodb_buffer_pool_size.

( (Com_show_create_table + Com_show_fields) / Questions ) = (468502 + 509251) / 40574407 = 2.4% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

( bulk_insert_buffer_size / _ram ) = 8M / 129024M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.

( (Queries-Questions)/Queries ) = (348646859-40574407)/348646859 = 88.4% -- Fraction of queries that are inside Stored Routines.
-- (Not bad if high; but it impacts the validity of some other conclusions.)

( Created_tmp_tables ) = 212,256,483 / 1314928 = 161 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 1,461,983 / 1314928 = 1.1 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( tmp_table_size ) = 1024M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Select_full_join ) = 1,396,307 / 1314928 = 1.1 /sec -- joins without index
-- Add suitable index(es) to tables used in JOINs.

( Select_full_join / Com_select ) = 1,396,307 / 15893105 = 8.8% -- % of selects that are indexless join
-- Add suitable index(es) to tables used in JOINs.

( Select_scan ) = 192,567,904 / 1314928 = 146 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 192,567,904 / 15893105 = 1211.6% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 374362 - 360454 ) / ( 374362 + 360454 ) = 1.9% -- Are you closing your prepared statements?
-- Add Closes.

( Connections ) = 7,761,574 / 1314928 = 5.9 /sec -- Connections
-- Increase wait_timeout; use pooling?

Extremes The following are noted, mostly without comment:

Abnormally small:

Com_commit = 0.95 /HR
Com_kill = 0.0027 /HR
Com_show_collations = 0.0082 /HR
Com_show_events = 0.0082 /HR
Com_show_processlist = 0.025 /HR
Innodb_log_writes / Innodb_log_write_requests = 0.02%
Key_blocks_unused = 3,946
Threads_connected = 0.0055 /HR
innodb_lru_scan_depth / innodb_io_capacity = 0.0341
key_buffer_size / _ram = 0.01%
query_cache_limit = 131,072

Abnormally large:

Com_create_procedure = 0.84 /HR
Com_drop_procedure = 0.84 /HR
Com_show_create_table = 0.36 /sec
Com_show_table_status = 0.35 /sec
Com_show_tables = 0.38 /sec
Com_unlock_tables = 0.35 /sec
Handler_delete = 88 /sec
Handler_read_first = 156 /sec
Innodb_buffer_pool_pages_data = 5.12e+6
Innodb_buffer_pool_pages_misc = 438,469
Innodb_buffer_pool_pages_total = 5.57e+6
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 93.1
Innodb_ibuf_merged_delete_marks = 243 /sec
Innodb_ibuf_merges = 2.5 /sec
Innodb_log_write_requests = 673 /sec
Innodb_rows_deleted = 89 /sec
Open_table_definitions = 20,252
Open_tables = 20,000
binlog_cache_size = 1.05e+6
group_concat_max_len = 3.36e+7
innodb_io_capacity = 30,000
innodb_io_capacity_max = 60,000
innodb_open_files = 20,000
max_heap_table_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 1024MB
performance_schema_max_cond_instances = 44,100
performance_schema_max_statement_classes = 181
table_definition_cache = 20,000

Abnormal strings:

Compression = ON
block_encryption_mode = aes-256-cbc
init_connect = SET NAMES utf8
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_force_load_corrupted = OFF
innodb_large_prefix = ON
innodb_numa_interleave = ON
locked_in_memory = ON
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
secure_file_priv = /tmp/
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
tx_isolation = READ-COMMITTED


How do I allow users to add members to a specific database role? I want to GRANT permissions to a new database role so that defined users can add and remove members from that role WITHOUT granting ALTER ANY ROLE permissions.

Here's how my query runs/dies:

CREATE ROLE [NewDBRole]
GO

The command(s) completed successfully.

GRANT ALTER ON [NewDBRole] TO [domain\username] WITH GRANT OPTION AS DBO
GO

This statement returns this error:

  Server: Msg 15151, Level 16, State 1, Line 1
  Cannot find the object 'NewDBRole', because it does not exist or you do not have permission.

But clearly the Role exists and I do have permissions, because I am able to DROP it:

DROP ROLE [NewDBRole]
GO

This statement works fine and returns:

  The command(s) completed successfully.

Is there special syntax needed for this GRANT, and if so, what syntax will be required to REVOKE it in the future?

Look at the syntax diagram a little more carefully. Typically, above object level, you must specify the type you are applying permissions to with <entity_type>::<entity_name>.

GRANT ALTER ON ROLE::[NewDBRole] TO [domain\username] WITH GRANT OPTION AS dbo;


View created from XML data - Statistics I am looking at a legacy system that stores XML blobs

A view is created over this so the data is more accessible (for reporting)

CREATE view [dbo].[Data]
as
select top 100 percent
data.value('(/Input/@FirstName)[1]','varchar(100)') as FirstName,
(select data.query('for $i in /Input/Control[@Name="Postcode"]/* order by $i/@UpdateDateTime descending return $i')).value('(/Value/@ValueCode)[1]','varchar(50)') as 'Postcode'
...
...
from data

Will SQL server generate statistics for queries run against this view?

No, the query optimizer will not create statistics for a view unless it's an indexed view, you can create an XML index on the xml column itself to speed up the view,


You could try simplifying and using LATERAL for joining the laps table:

SELECT
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM
  races
LEFT JOIN
  timers AS tmptimers
    ON  tmptimers.user_id = 1
    AND tmptimers.race_id = races.id
LEFT JOIN LATERAL
  ( SELECT
      updated_at AS last_updated_at
    FROM
      laps
    WHERE
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    ORDER BY
      updated_at DESC
    LIMIT 1
 ) AS tmplaps
     ON TRUE
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at). I tested a similar query on SQL-Fiddle and it used the index. The plan showed:

  Index Only Scan Backward using "index_name" on laps


Is OPTION (RECOMPILE) used in production environments? Is OPTION (RECOMPILE) used in production?
This option seems to get a lot of bad press. Is it deserved?
I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of Report ETL ssis agent queries.  These queries are executed (to the best of my knowledge) sequentially and at scheduled intervals.
Back History:

SQL Server 2016
ETL Queries that cause clustered index scans when run through the ssis agent. These queries take minutes to complete and cause heavy impact.
The same query and parameter run via a local stored procedures executes in less than a second.

Wait are you certain OPTION (RECOMPILE) is the answer?

Unknown.
But I need to know whether this is a really bad idea before I try.

The risks I'm aware of:

There have been at least two serious bugs in the past related to
OPTION (RECOMPILE). Two queries run at the same time could swap
result sets. Ugh !!
https://support.microsoft.com/en-us/topic/fix-rare-possibility-of-incorrect-results-when-you-use-option-recompile-for-queries-inside-a-procedure-in-sql-server-2014-or-sql-server-2012-c247fbb5-4125-dd0f-7789-7b1c126f8241

So given the above - is this option actually used in the real world? Is it acceptable that I recommend (and test) this as an option for a production environment?

UPDATE
I was asked to provide more details. I mentioned that I do have other posts all related to this topic.  Let me give more information on that:

The root problem is that queries coming from an application server
are taking longer than 60 seconds. Normally these queries take 4 to
10 seconds. Through a lot of pain, I've determined that the time outs
line up with the ETL queries.  4 queries out of 15 to be specific.
A contributor to the problem is found within the application servers. Specifically the isolation level is set to serializable within the hibernate layer; which I have learned is not optimal for high volume production environments.

Let me share the other questions:
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

OPTION(RECOMPILE) is used in real word production scenarios. I've employed it to address parameter sniffing and optimize kitchen sink queries. It might be the answer for your issue but the symptoms suggest OPTIMIZE FOR UNKNOWN (same as local variables) may address the problem as well.
I certainly would not avoid an option just because a bug once existed, and it was fixed several years ago. The main risk with OPTION(RECOMPILE) is when it's used inappropriately, such as high-frequency queries.


Securing an Internet Database Connection I am looking to setup a website and database that are connected via the internet, and am looking to secure the connection. The connection will probably not be over a VPN, so shall be openly exposed.

From the research I have done, I see that you can use SSL and IPSec. The user I am setting up will only have public, db_datareader and db_datawriter roles. The password will be set to expire every set period of time.

Is there anything else I should be aware of to ensure a secure connection from my website to database?

You DB should probably not be publicly accessible, unless it really has to be. You can make DB accessible only to localhost, which would allow your web server to talk to it and you can use it over SSH or Remote Desktop or what have you.
You'll want to watch out for brute-force attempts on your logins. I'm not sure what the Windows equivalent of Fail2Ban is, but you'll want something like that.
Shut off any unneeded db services, see what ports are open, use a firewall to block any ports other than the ones you definitely need.
Have your access logs recorded to another computer with a write-only connection, so that if someone does break in, you still have logs.


SSAS Dimensions Hierarchy causes deployment errors I've set up a Data Source Connection to the Adventure Works DW database using the service's credentials.

I'm creating a dimension using the DimDate table.  To simplify things, I'm using only the Calendar Quarter, Calendar Year, Day Number of Month, English Month Name, and the Date Key columns.

Each of these attributes have been set to AttributeHierarchyVisible = False.

I dragged the Calendar Year into Hierarchies followed by Calendar Quarter, English Month Name, Day Number of Month.

I set the attribute Relationships to:

Date Key -> Day Number Of Month -> English Month Name-> Calendar Quarter -> Calendar Year

All relationships are set to rigid.

When I try to deploy this setup, I get the following errors:

  Error  2   Internal error: The operation terminated unsuccessfully.        0   0
  Error   3   Server: The current operation was cancelled because another operation in the transaction failed.        0   0

I also receive this warning:

  Warning    1   Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimDate', Column: 'CalendarQuarter', Value: '2'. The attribute is 'Calendar Quarter'.      0   0

I can't figure out what is going on, or why this is erroring.  The book I'm using gave me the steps listed above and Google has failed me thus far (a very scary thing).

Any help you guys can give to help me figure out what I'm doing wrong would be greatly appreciated.

I feel you're treating the symptoms and not the cause by suppressing errors.

  I set the attribute Relationships to Date Key -> Day Number Of Month -> English Month Name-> Calendar Quarter -> Calendar Year

If that data looks like:

"16062000"--> 6 --> "June" ---> "Q2" --> 2000

then Analysis Services will indeed get the hump, as for the Q2 it will have a Q2 feeding 2000 and 2001 etc.

You have to make sure the hierarchy consists of unique keys e.g.:

16062000--->062000-->Q12000--->2000

You can use different fields for the Name property of each attribute, but use the keys for the keys. Oh and if you sort by key on the month, this will prevent the classic sorting of months alphabetically.


Gathering information to tune oracle row prefetching I'm trying to determine a value for the number of rows an oracle client will prefetch (i.e. OCI_ATTR_PREFETCH_ROWS, exposed as setFetchSize in JDBC) that will minimize the number of server round trips without excessively increasing memory usage.

To do this, I need to gather information on queries either from the application or from the database. Can oracle provide disaggregated historical information about

how many rows were in the results of queries
how much data was returned for queries

Here is an example of the type of report I'm interested in generating:

  On the instance named foo, 20,000 queries were executed on Monday.

  60% of the queries returned fewer than 100 rows, 20% returned
  between 100 and 1000 rows, and 20% returned between 1000 and 10,000
  rows.
  50% of the queries returned less than 1KB of data, 30% returned
  between 1KB and 10KB, and 20% returned between 100KB and 1,000KB

If the database server cannot provide this information, but you have ideas for other ways I could gather it, feel free to answer with those.

This is an interesting question and differs significantly from the approach I have used.  The kind of information you are looking for would not be of much interest to me.  I would be more concerned with the number of rows displayed (or processed) for each query.  The place to capture that is likely in your application(s).

You may be able to gather most of what you are looking by enabling auditing for your queries and analyzing the audit data.

For batch retrieval which will use the full result set, I generally set a relatively large pre-fetch value (100 or so).  Tuning might be of value in some cases, but I would limit it to frequently run queries with large result sets where database fetch is a significant portion of the overhead.

For interactive applications I usually tune prefetch limits towards the number of items displayed on a page.  There isn't much value in prefetching data for the next page unless the users actually page to it.  It has been my experience, that users don't page down that often.  Anything past the second page would be overkill for the initial prefetch.

EDIT: Based on your comment, setting prefetch size to one or two screens of data may decrease latency on the link.  Fetching unused data will increase the load on the link.  Setting the link to prefetch data should also reduce the load on the link.


combine 4 queries in one I have 4 queries I want to combine in one to reduce DB calls. Each interval list the products for that time interval and is used for a specific sub treatment.

select * from product where add_time >= NOW() - INTERVAL '12 hour' (get product add the last 12h)
select * from product where add_time >= NOW() - INTERVAL '24 hour' (get product add the last 24h)
select * from product where add_time >= NOW() - INTERVAL '7 day' (get product add the last week)
select * from product where add_time >= NOW() - INTERVAL '30 day' (get product add the last month)

Is it possible? what will the query look like and how to access each group interval (12h, 24h, 7 day, 30 day) to process the result?

PostgreSQL 9.5.12

Just do it in the client if you must.

SELECT *
FROM product
WHERE add_time >= NOW() - interval '30 day';

But if you must you can offload some of this to the server, thought I wouldn't because it's largely view-logic.

SELECT
  add_time >= NOW() - interval '12 hours' AS 12_hours,
  add_time >= NOW() - interval '24 hours' AS 24_hours,
  add_time >= NOW() - interval '7 day' AS 7_day,
  *
FROM product
WHERE add_time >= NOW() - interval '30 day';

You could also put it all into a case statement if you really wanted...

SELECT
  CASE
    WHEN add_time >= NOW() - interval '12 hours' THEN '12h'
    WHEN add_time >= NOW() - interval '24 hours' THEN '24h'
    WHEN add_time >= NOW() - interval '7 day' THEN '7d'
  END,
  *
FROM product
WHERE add_time >= NOW() - interval '30 day';


Oracle : Redo / Undo for nologging table + Append + archivelog I'm trying to replicate a table from an external Oracle DB to my system using a DB Link.

First i tried doing it using an mview but it caused alot of redo/undo(I did the same steps as below but it didn't work for some reason).

Next, I went on a different strategy and created a self implementation of an mview with a table.

Table was set as nologging when created. (verified it in dba_tables)
Index on table is set as nologging when created. (verified it in
dba_indexes).
Database log_mode is set to ARCHIVELOG. (verified in v$database).

process of replication is being done like:

Set index as unusable.
Truncate table.
Insert with append hint.(INSERT /*+append*/ into tbl_name SELECT *
FROM v_tbl)
Rebuild index nologging.

The code is written in a package which is being run with a scheduler_job on an interval.

v_tbl - a view that encapsulate the original external table structure(view is being built dynamically when compiled due to different sites external DB table names and columns) and give constant names for the columns(by the view columns aliases).

v_Tbl :

 SELECT column1 col1,
        column1 col2
 FROM ext_tbl@external_db;

When examining the archives using dbms_logmnr package I found a lot of redolog is being written on this table even though I'm using the append hint and no logging options for table and index.

In v$logmnr_contents view I see alot of rows for this table with operation = "DIRECT INSERT" and I can see all the data which was inserted in the redo/undo columns.

Is it related maybe to the DB link?

Am I doing anything else wrong?

It looks like you set your indexes to unusable.
I believe that just means the index won't be used if you query the table.

However, during your loading, the index will be maintained with inserted and updated values, thus generating redo even when nologging is used.

"nologging" for index will only work when used with create index statement.

There may be other reasons but this is one of them.

Try the following and see if it generates less or no redos:

Drop indexes
Load table with /*+APPEND*/(will be faster too without indexes on
the table)
Create index with "nologging" option


Help with updating and archiving data I'm building a MySQL database and a frontend.  One function of the database application is to make an invoice.  I'm looking for a way to archive all of the data that went onto the invoice so that if some data changes, the invoiced data is still available.

For example:
On table customers,

|---------+-------------------+-------------|
|  name   | email             | phone       |
|---------+-------------------+-------------|
|  George | george@domain.com | 3179094235  |
|---------+-------------------+-------------|

Suppose I make an invoice for George in year 2017.  In 2018, George changes his phone number.  I want my database to have a current phone number for George.  Now in 2018, my customers table looks like

|---------+-------------------+-------------|
|  name   | email             | phone       |
|---------+-------------------+-------------|
|  George | george@domain.com | 2024123987  |
|---------+-------------------+-------------|

but I want the data that my invoice used to still be used in 2018 when I generate the invoice document again.

In the real world, much more data will be put onto the invoice.  How can I make it so that the invoice that I generate in 2017 will use all of the same data as the one I generated in 2018, even if I've updated the data in my database?  Invoice is currently generated from data from multiple tables: parts, labor, customer info, etc. Can I archive all of the invoice data to a table, and always generate invoices from the archive table? Snapshots? Transactions?  I'm new to databases, so I don't know if I'm looking for a MySQL feature or a database design technique, or something else.

You have two things.  You need two tables to handle such.

A record of Invoices.  Once an invoice is issued, the row(s) that describe must not be modified.  There can be many rows for George, assuming he is issued many invoices.
Current information about George, etc.  This is updated as needed; there is one row for George.

It may be that, when issuing a new Invoice, you need to reach into the other table to update that info.  So, simply plan on always issuing two statements, preferably in the same transaction:

INSERT INTO Invoices ...;
UPDATE Current ...;   -- No harm if nothing actually changes.


Find the highest graded student using Tuple Relational Calculus Suppose I have a table of students, containing their ID and their grade:

-----------------
| id  |  grade  |
-----------------
|  1  |    83   |
|  2  |    94   |
|  3  |    92   |
|  4  |    78   |

How do I write a Tuple Relational Calculus formula that refers to the student with the highest grade?

My attempt:

Thinking in terms of SQL, I would write a query that does a cartesian product of the table with itself, take every grade that is less than some other grade, and then subtract from the original table. However, in Tuple Relational Calculus, it's not possible to build sub-tables in sub-queries, which is why I'm stuck.

However, I've attempted something in this direction:

{ <id> | Ǝ grade1 ∈ students (id, grade) ⋀ Ǝ grade2 ∈ students (id, grade2) ⋀ grade1 > grade2}

I believe this would get me the lower grades, but how do I subtract this all from the original table of students? I'm not allowed to insert this statement into another TRC query. Thanks in advance for your help!

Given the schema Students(id:integer,grade:integer), you can solve the problem in tuple relational calculus by using the negation operator (¬).

{T1.id | ∃T1 ∈ Students ¬(∃T2 ∈ Students (T2.grade > T1.grade))}

This would return the id of all students in T1 where there is no student in T2 with a higher grade.

Because T1 and T2 are from the same relation, this effectively returns the set of students with the top grade.

If there are no ties for the top grade, it will return just one id.

It looks like you were thinking in terms of relational algebra rather than tuple relational calculus.

Tuple relational calculus does not have a set difference operator, so you can't find the maximum by subtracting all non-maximums.

Reference

The Solutions Manual for the third edition of Database Management Systems by Ragu Ramakrishnan and Johannes Gerke helped me solve this.

Question 4.3 asks you to solve some problems using this schema:

Suppliers(sid:integer,sname:string,address:string)
Parts(pid:integer,pname:string,color:string)
Catalog(sid:integer,pid:integer,cost:real)

Question 4.3.11 is similar to yours.

  Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.

The solution looks like this:

{T | ∃T1 ∈ Catalog(∃X ∈ Suppliers
(X.sname = 'Yosemite Sham' ∧ X.sid = T1.sid) ∧ ¬(∃S ∈ Suppliers
(S.sname = 'Yosemite Sham' ∧ ∃Z ∈ Catalog
(Z.sid = S.sid ∧ Z.cost > T1.cost))) ∧ T.pid=T1.pid)}

The solution here is more complicated because of the joins between Suppliers and Catalog, but the essence of the solution is the same.

Elide the joins to get this:

T1 ∈ Catalog ...
(... ¬(... ∃Z ∈ Catalog
(... Z.cost > T1.cost)))

In the reference solution, free variable T has the same pid as bound variable T1.

Taking the pid of T1 directly is a simpler way to get the same result.


MS SQL Row-Level-Security out of a Citirx Store Front I’m looking for either a solution, or a definitive answer that what I want to do can’t be done.
My situation:
I have a MS Access Front End that is located in a Citrix Store Front.
The Front End connects to a 2017 MS SQL Server Backend.
The server backend is configured to use Row-Level-Security as described in this article.
Out of the Citrix Store Front, my Access Front End connects to the server, but the row-level-security only recognizes the user and password that I send via the access connection string, not the user that opens the Access Front End. This makes sense to me, since I’m passing a User ID and Password via the connection string.
The idea of having the MS Access Front End in the Citrix Store Front, is that our IT does not want to open up the firewall for each individual user, and that thus we can use one connection string for everyone.
My Question:
Given that this is the case, can I still implement Row-Level-Security, or is it not possible under the one connection string scenario?

The only way you have here is to use INTEGRATED SECURITY in the connection string.
In this way the connecting login is also used against the database but if there is a firewall between database and frontend that recognize windows authentication (it filters in application layer i suppose) every users need to be included in the allow rule. Usually, it is easily done using domain groups.


Rebuild and Reorganize index while altering index Can someone please explain for example if i have a unique non-clustered index on a table with 3 columns.
due to some reason if i need to add one more column in the existing index .

Do i need to rebuild index ?

When you add/remove any column in existing non-clustered index, the index will be recreated(not altered) and hence index rebuild is not required.

The larger your tables, the greater the impact of executing DDL statements against indexes, both in the consumption of server resources and in the slowing of other queries that are executing at the same time.

On Enterprise Edition, you can choose to use CREATE INDEX  ... WITH (DROP_EXISTING = ON, ONLINE = ON) (for certain index changes at least) to minimize interference with other queries (they can continue using the original index while the new one is building, at the cost of the additional disk space / activity required to maintain both). And in 2019 you can use RESUMABLE so, you can pause the creation of the new index during high activity, or spread the operation across multiple maintenance windows.


Oracle Application Server 10g Installation Process I downloaded Oracle Application Server 10g from this Oracle site. It has 4 .cpio files for the Linux installation. How do I unzip and install it on VMWare?

A cpio is a file containing other files, similar to tar files. Usually your system (you mentioned Linux but not which distribution) will contain GNU cpio. you can list the file sin an archive by using cpio -it < file.cpio
 and extract it using cpio -i < file.cpio.

I don't know anything about Oracle Application Server (as this is the dba site: mind that Oracle Application Server is not the Oracle Database, that's a different download) but assume the first "CD" contains an installer script and an installation manual.


DBCC DBINFO showing wrong information Though DBCC DBINFO() is undocumented, but we all know that

  dbi_crdate: Database creation datatime
  dbi_dbccLastKnownGood: the
  completion time of the last 'clean' run of DBCC CHECKDB

For one of my database its showing following output.

Why output of DBCC DBINFO() showing smaller value for dbi_dbccLastKnownGood as compare to dbi_crdate. How can someone execute DBCC CHECKDB before a database is created ?

If you restore a database as a new database, the dbi_crdate field is the restore date/time.

Here's how to reproduce it:

Create a new database.
Run DBCC CHECKDB() on it.
Look at DBCC DBINFO() for the creation date and DBCC dates.
Back up the database.
Delete the database.
Restore the database (if you want to get fancy, use a different database name.)
Look at DBCC DBINFO() for the creation date and DBCC dates. The creation date will be the restore time, and the DBCC date will be earlier.


How to find Oracle error log in 11gR2? I am facing issues with Oracle log files. My application inserts data from xml file into Oracle. I started my application last day and went home. It stopped unexpectedly at 8PM. There is no log in application why the insertion stopped.

I want to check Oracle log file to check what exactly the error was and why the Oracle stopped inserting data.

I have log.xml file and multiple *.trc and *.trm file. I don`t know how to start with this.

I just have to check the error that why the Oracle has stopped.

Aside from what maialithar said, you can also check the alert_yourSID.log or the listener.log


Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script? Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy way/script to do that?

Yes, using DO, cursors, execute, and the pg_namespace table:

DO $$DECLARE _schema record; _user name := '<your_username>'
BEGIN
  FOR _schema IN
    SELECT * FROM pg_namespace WHERE nspowner > 10 OR nspname = 'public'
  LOOP
    EXECUTE 'GRANT SELECT, INSERT ON SCHEMA '||
            quote_ident(_schema.nspname)||' TO '||quote_ident(_user);
  END LOOP;
END;$$;


Getting stuck with creating better index (Optimize Join) Im using the below query and it took min 5s to max 11s. I tried to create single column and multi-column index. But still I can't able to increase it's performance.

Row count on each table:

tbl1 - 400000
tbl2 - 200000
tbl3 - 50

Table definition:

CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `customer` varchar(155) DEFAULT NULL,
  `social` enum( 'Facebook','twitter') DEFAULT 'LI',
  `grade` char(1) DEFAULT NULL,
  `Monthly` int(11) DEFAULT NULL,
  `Off_comments` text,
  `createDate` datetime DEFAULT NULL
  );

create table tbl2
(
id int  NOT NULL
);

create table tbl3 (
id int, frnd_id int
);

Select Query:

SELECT    `T1`.*,
          `T2`.`Card_No`
FROM      `tbl1` `T1`
LEFT JOIN `tbl2` `T2`
ON        T1.id = T2.id
LEFT JOIN `tbl3`
ON        T3.id = T2.On_site_id
WHERE     T1.modified<=Subtime(Now(),'00:00:10')
AND       T1.customer != 'bhuvi'
AND       T1.social !='Facebook'
AND       T1.social != 'twitter'
AND       T3.frnd_id= '4'
AND       T1.grade IN ('A','B', 'O')
AND       (
                    T1.Monthly IS NULL
          OR        T1.Monthly = 1)
AND       (
                    Off_comments IS NULL
          OR        Off_comments = ''
          OR        Off_comments="''")
ORDER BY  `T1.createDate`;

Indexed columns:

tbl1 - id,modified,customer, social,frnd_id
tbl2 - id
tbl3 - id

Explain plan:

Filesort
+- TEMPORARY
   table          temporary(tbl3,T2,T1)
   +- JOIN
      +- Filter with WHERE
      |  +- Bookmark lookup
      |     +- Table
      |     |  table          T1
      |     |  possible_keys  fk_T_1_idx
      |     +- Index lookup
      |        key            T1->fk_T_1_idx
      |        possible_keys  fk_T_1_idx
      |        key_len        5
      |        ref            db.T2.id
      |        rows           1
      +- JOIN
         +- Index lookup
         |  key            T2->id
         |  possible_keys  PRIMARY, id
         |  key_len        5
         |  ref            db.t3.id
         |  rows           2537
         +- Index lookup
            key            tbl3->INX_frnd_id
            possible_keys  PRIMARY,INX_frnd_id
            key_len        5
            ref            const
            rows           16

First, let's clean up the data.

Is Monthly a true/false value?  Then use 0/1 and put it in TINYINT NOT NULL.  Or is it tri-state:  Yes/No/unspecified?

If Off_comments is an optional string, then cleanse the data so that the empty string and '' are turned into NULL when it is stored.  (Meanwhile, one or two UPDATEs can clean up the data.)

I recommend those because OR is hard to optimize, and this will probably eliminate such.

LEFT says that the 'right' table is optional.  Yet AND t3... says that you must find a row.  So change both LEFT JOINs to JOINs.

Once that is done, it may be efficient to check for T3.frnd_id= '4' first instead of last.  Could it be that there aren't many rows with 4?

Indexes...

Assuming the above works, T3 needs INDEX(frnd_id, id), then T2 needs INDEX(On_site_id, id).

If the optimizer prefers to start with T1, first look for any = things in the WHERE.  But, the only possibility is monthly, depending on the discussion above.  Off_comments is not a candidate for indexing since it is TEXT.

I'll digress a moment.  What is T3 about?  It has only 50 rows?  Perhaps it is "over-normalization"?  If so, couldn't you simply put frnd_id into T2?

Grrr... Have you "simplified" the question?  This seems invalid:  social enum( 'Facebook','twitter') DEFAULT 'LI', since LI is not an option in the enum!

Grrr-squared...  This is probably not what you wanted:

`T1.createDate`

Instead:

`T1`.`createDate`

Please fix those and any other mis-simplifications!

T1 may be able to use INDEX(createDate).

One more thing:  1:1 relationships (T1.id = T2.id) are usually a questionable design.  Is id the PRIMARY KEY on both tables?


Does avg_query for PgBouncer include update and inserts? Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:

avg_query
    Average query duration in microseconds.

includes the average duration for INSERTs and UPDATEs as well as SELECTs.

Thanks very much!

Yes - pgbouncer does not know what type a query is, it just passes it through to the backend. Therefor, avg_query is across all queries, including both INSERT, UPDATE and DDL statements.


What are the SQL Server 2012 versions of FIRST() and LAST()? I have a table with a value column.  I want to calculate the last row minus the first row, as shown here:

 id      value
  1       10
  2       45
  3       65
  4       95
  .       .
  .       .
  .       .
 500     200

I want to obtain 200 - 10 = 190

I've tried to use the below command in SQL Server 2012, however LAST and FIRST don't work.

SELECT LAST(Value) - FIRST(Value) FROM Counter;

What is the syntax for this command in SQL Server?

You were close - FIRST and LAST are from Access; in SQL Server (starting with SQL Server 2012) they are FIRST_VALUE() and LAST_VALUE().

So, if you are 2012 or better (or Azure SQL Database), here's one way to get your answer:

CREATE TABLE #fl
(
  IdentityColumn INT IDENTITY,
  Value INT
);

INSERT #fl(Value) SELECT 10;
INSERT #fl(Value) SELECT 45;
INSERT #fl(Value) SELECT 65;
INSERT #fl(Value) SELECT 95;
INSERT #fl(Value) SELECT 200;

SELECT TOP (1) LAST_VALUE(Value) OVER (ORDER BY IdentityColumn)
            - FIRST_VALUE(Value) OVER (ORDER BY IdentityColumn)
  FROM #fl
  ORDER BY IdentityColumn DESC;

GO
DROP TABLE #fl;


Filter Schema In Index Optimize Script Is it possible to do index optimization with Ola's scripts and have it filter out a specific schema?

Yes, if you look at the documentation, you see that you can use the @indexes parameter to specify which indexes to defragment. You can use % wildcards in your object names and you can prefix them with - to exclude indexes.

Using this information, the syntax you want to use is @indexes = 'ALL_INDEXES, -databasename.excludedschema.%', or @indexes = 'ALL_INDEXES, -%.excludedschema.%' if you want to exclude the schema in all databases.

Below you can find a repro documenting this:

First create a database with 2 fragmented tables, one in the dbo schema and one in a schema I called Exclusion. Both should be around 99% fragmented.

USE [master];
GO

CREATE DATABASE [196090];
GO
USE [196090];
GO

SET NOCOUNT ON;
GO

-- Create a table in dbo schema
CREATE TABLE [ProdTable] (
    [c1] INT,
    [c2] CHAR (5000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]);
GO

-- Fill with random integers to create fragmentation
INSERT INTO [ProdTable] (c1, c2) VALUES  (CRYPT_GEN_RANDOM(8000), 'filler');
GO 12800

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'196090'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO

-- Create a schema to exclude from the rebuild
CREATE SCHEMA Exclusion;
GO

-- Fill with random integers to create fragmentation
CREATE TABLE [Exclusion].[ProdTableExclude] (
    [c1] INT,
    [c2] CHAR (5000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [Exclusion].[ProdTableExclude] ([c1]);
GO

INSERT INTO [Exclusion].[ProdTableExclude] VALUES  (CRYPT_GEN_RANDOM(8000), 'filler');
GO 12800

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'196090'), OBJECT_ID (N'Exclusion.ProdTableExclude'), 1, NULL, 'LIMITED');
GO

Then run Ola's index maintenance this way:

EXECUTE dbo.IndexOptimize
@Databases = '196090',
@indexes = 'ALL_INDEXES, -196090.Exclusion.%'

And you will notice the script will skip the schema, leaving one of the indexes fragmented.


Cannot login in as root user after MySQL installation (RHEL) I am new to MySQL.
I installed MySQL (version 8) in RHEL 7.7 using yum . Now , I am trying to configure MySQL.

[root@devdbvm278 ~]# cat /etc/my.cnf
# commented lines removed for readability
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Once the installation was done, I started the mysqld using

# systemctl start mysqld

Now, when I try to log in as root user, I get the following error.
I tried to fix it using some stuff I found from googling. But, no luck.
Any idea what is wrong ?

[root@devdbvm278 ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@devdbvm278 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@devdbvm278 ~]#

[root@devdbvm278 ~]# mysqld_safe --skip-grant-tables
-bash: mysqld_safe: command not found

[root@devdbvm278 ~]# mysqld --skip-grant-tables
2019-08-13T15:53:33.812294Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 19578
2019-08-13T15:53:33.815432Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2019-08-13T15:53:33.815507Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-08-13T15:53:33.815777Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.17)  MySQL Community Server - GPL.
[root@devdbvm278 ~]#

# mysqld --version
/usr/sbin/mysqld  Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

In the security section of the documentation it states:

  If you do not know the initial random password, look in the server
  error log.

Try running the following command. It works for me on my 5.7 installation (which has the same instructions as the 8.0 version of the documentation):

sudo grep 'temporary password' /var/log/mysqld.log

It should tell you the initial password to use for root.


cannot insert multiple commands into a prepared statement This query works for me when I tried in PG admin query tool:

UPDATE bankdetails SET bank_details = 'mysore' ,"PAN"= 123,bank_acc = 456,
                       "UAN"=789,tax = 'myy'
WHERE  user_id = 79;
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
       SELECT 79,'mysore',123,456,789,'myy'
       WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id=79);

Later I implemented in my code like this:

db.query('UPDATE bankdetails SET bank_details = $2 ,"PAN"= $3,bank_acc = $4,"UAN"=$5,tax = $6 WHERE user_id = $1; INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax) SELECT $1,$2,$3,$4,$5,$6 WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id = $1);', [companyuser.rows[0]._id, data.bankDetails.bank_details, data.bankDetails.PAN, data.bankDetails.bank_acc, data.bankDetails.UAN, data.bankDetails.tax],function(err,bank) {

                if (err) return callback(new Error('error'));
})

it says:

  cannot insert multiple commands into a prepared statement

Can some one guide me here?

You can do that in a single query using a writeable CTE:

with updated as (
   UPDATE bankdetails
       SET bank_details = 'mysore',
           "PAN"= 123,
            bank_acc = 456,
            "UAN" = 789,
            tax = 'myy'
  WHERE bankdetails.user_id = 79
  returning *
)
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
SELECT 79,'mysore',123,456,789,'myy'
WHERE NOT EXISTS (SELECT * FROM updated);

The insert will only be executed if the update did not change a row.

You can even avoid writing the values twice:

with data (user_id, bank_details, pan, bank_acc, uan, tax) as (
  values (79, 'mysore', 123, 456, 789, 'myy')
), updated as (
   UPDATE bankdetails
       SET bank_details = d.bank_details,
           "PAN"= d.pan,
            bank_acc = d.bank_acc,
            "UAN" = d.uan,
            tax = d.tax
  FROM data d
  WHERE user_id = d.user_id
  returning *
)
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
SELECT user_id, bank_details, pan, bank_acc, uan, tax
FROM data
WHERE NOT EXISTS (SELECT * FROM updated);

I don't know which programming language you are using, but for e.g. a JDBC PreparedStatement you can replace the data in the values(..) part with parameter placeholder:

with data (user_id, bank_details, pan, bank_acc, uan, tax) as (
  values (?, ?, ?, ?, ?, ?)
)
...


Web application for Software as a Service - Shared database or Multiple Databases? (I am not sure if this is the best place to post this question. If not, please redirect me to the appropriate stack exchange site.)

I am looking to deploy a web application for multiple clients. The idea is that each client can only access his or hers own data. Let's say that the client is the owner of the application installation. Each installation can have multiple users. All installations will be in my own hosting, so that I can better control the code and business model (its a SaaS kind of thing).

I am looking for technical options to perform this kind of deployment.

[Option 1] - Shared single database

On easy option to do this would be:

change the database in order for each relevant table to know its
"owner id".
change all SQL queries so that each User will only fetch
data that is related to its "owner id" (I don't have the requirement
for an User to be common to more than one client/installation).

However, this solution has a few drawbacks:

requires changing most of the SQL code;
there might be some performance impact since the database will have much more data (the data for all installations).
mixing data of different clients in the same database may cause some issues (if there is a bug, its easier for someone to end up accessing some else's data);

So I would prefer an alternative where the data would be isolated.

[Option 2] - Multiple databases

One alternative that I was considering was:

For each installation, buy a DB from the hosting company
Come up with a configuration scheme that allows associating each installation with the database where the connection should be performed.
For example, if the user accesses http://very-nice-app.com/client-3/ the code knows that it must connect to database-name-for-client-3.

This solves the potential issue of data leaking, but requires me to maintain several databases (if DB tables change, it has to be done across multiple installations). This can probably be automated somehow.

[Questions]

What would be the better approach? Is there a better alternative than the ones I have indicated?

Thanks in advance for your help.

[Additional info]

The database engine will probably be MySQL.
This is a startup, so we are trying the business model. We don't have a good idea of how many clients we will have - it will be zero for some time. Right now we are looking for the ability to deploy and scale for a few clients (lets say up to 10).
This app is targeted at small business and the amount of data should not be in millions of records. The largest table should have about 10.000 records per year (per client). This value is based on the company with whom we did the prototype (which is a mid sized company for the area x business).

This is the old "how to handle multi-tenancy" question for which there is no one right answer. You have covered a good selection of the key points. I can't provide an answer beyond "it depends" but here are a couple of other points for you to consider:

mixing data of different clients ... if there is a bug, its easier for someone to end up accessing some else's data and Come up with a scheme associating each installation [correct] database - the risks here are very similar if the various databases are on or can be accessed by the same hosts, especially from the point of view of a defending against deliberate attacker, so that part of the decision comes down to which collection of potential security concerns you feel you can efficiently design around most effectively. If you feel your SQL skills are lacking that might push you in the multiple database direction due to concerns about large data sizes become inefficient as your client base grows.
For each installation, buy a DB from the hosting company for a SaaS solution you have an inappropriate hosting arrangement if you are paying per database or similar. It sounds like traditional shared web hosting were you should be looking at dedicated machines (virtual or physical) or a PaaS based platform.
Consider carefully how you intend the application to scale. The multiple databases option gives you the freedom to split clients between database servers as your needs grow, but the single database option is easier to manage in a PaaS environment if you design is appropriate, letting the platform provider worry about the physical reality of scaling as you pay for more resources.
Also on the scalability matter, make sure consider your administrative responsibilities: how will you backup the data, restore parts of it if there is an error/accident, or restore all of it in the event of an application/platform wide catastrophe, and what timescales do you want/need to work to in such events?

For more information see https://msdn.microsoft.com/en-us/library/aa479086.aspx, https://en.wikipedia.org/wiki/Multitenancy, and search for similar terms for further discussion. (that first link is from MS, but covers the matter from a general design perspective rather than being specific to MS SQL Server, it has also been around a while but the matters concerned haven't changed as much as you might think the would have in the last decade)


how effective creating 2 database with same content? I'm in a dilemma if creating redundant database will speed up the access considering only the CRUD factor.

Is it effective to create a dedicated DB for creating/updating/deleting records
and a separate DB for viewing only?

Currently using MySQL.

This is called Write masters with Read slaves. Check out MySQL replication. You'll find an interesting document here (check out the slides) - but you probably won't be needing the level of availability of Booking.com, but hey, who knows? :-).

There are many different replication/HA topologies, but this will give you a start. Also, see here (Andrew Morgan is the author of MySQL Cluster).


Does PostgreSQL write first to WAL and then to data files? This is regarding the synchronous_commit in Postgres.

The synchronous_commit documentation says:

  Specifies whether transaction commit will wait for WAL records to be
  written to disk before the command returns a "success" indication to
  the client

So my understanding is that a transaction will be written to the database first and then WAL files. synchronous_commit determines whether this WAL write is done synchronously or asynchronously.

My problem is, from following documentation:

  Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage.

I got the understanding that Postgres first writes transactions to WAL files and then to the datafile (database).

Which one is the correct? Does Postgres write first to WAL and then to data files? Can any one give more details about how synchronous commits happen and how this relates to WAL?

Your understanding is not correct.

  Specifies whether transaction commit will wait for WAL records to be
  written to disk before the command returns a "success" indication to
  the client

means that the transaction will be marked as committed without waiting for the success message.  This may come somewhat later (usually the time difference is very-very small), do to some magic done by the storage (caching, for example).  This means that you issue the COMMIT;, then the WAL data is written to the cache and only after this written actually to the disk.  Writing to the data files may happen later, but your data have been persisted at this point.

Depending on the type of the cache and other things, there is a short time window with synchronous_commit = off when you may lose data if power is lost, for example.