Using a filtered index when setting a variable I know from other questions and posts that when SQL compiles a query plan it can only use a filtered index if the filtered index is guaranteed to be able to be used every time the query runs. This means you can't use a variable in the where clause because sometimes it might be able to use the filtered index and sometimes not.
One way around this is to use OPTION(RECOMPILE), so that the times it can use it, it'll get the filtered index.
Doing some testing, I found that this query can use a filtered index (note, I'm forcing the index just to prove a point):
SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

However, if I want to assign the result to a variable, I'm bang out of luck:
SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

results in:

Msg 8622, Level 16, State 1, Line 15 Query processor could not produce
a query plan because of the hints defined in this query. Resubmit the
query without specifying any hints and without using SET FORCEPLAN.

The query can clearly use the filtered index as it runs find when I don't want to save the output to a variable.
I have ways of rewriting this query to hard-code @variable to remove the problem, but can someone explain why the first query can use the filtered index, but the 2nd query can't?

The optimization that allows you to use the filtered index with a RECOMPILE hint is called the "parameter embedding optimization."  That's a process where the query parser replaces the variable reference with the literal value inside the variable.
See this post from Paul White for the reason why it doesn't work in your second case: Parameter Sniffing, Embedding, and the RECOMPILE Options

There is one scenario where using OPTION (RECOMPILE) will not result in the parameter embedding optimization being applied. If the statement assigns to a variable, parameter values are not embedded:

You can try this query:
SELECT @OutputVariable =
(SELECT MAX(table1.SomeDateField)
FROM   dbo.table1
WHERE  table1.filteredColumn = @variable
  AND  table1.filteredColumn = the_filtered_value
UNION ALL
SELECT MAX(table1.SomeDateField)
FROM   dbo.table1
WHERE  table1.filteredColumn = @variable
  AND  NOT table1.filteredColumn = the_filtered_value)

The reason this works is because you are bifurcating the filtered index i.e. only using it in a case when the variable matches the filter predicate. The optimizer cannot use the index without this because it can't guarantee the value matches the predicate.


What's the most efficient way to reclaim unused DB hard drive space? Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having.

We have a database that unintentionally ballooned to 300+ GB. We have turned off the option that caused this in our application, but we want to purge the offending data (in only 1 table) and reclaim the space it was taking up on the hard drive. We have tried truncating the table and then shrinking it on a copy of the production database, but this process takes about a week.

Is there a quicker/better/more efficient way to do this? I have read that shrinking a database is a taboo among DBAs because it introduces index fragmentation.

We are using Microsoft SQL Server.

"Best way" translates to quickest and least likely to cause us issues in the future (like index fragmentation might). We are trying to make this as quick as possible so that our database won't be tied up for several days deleting data and shrinking files.

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP NewFG;
GO

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = FG2,
    FILENAME = 'G:\DATA\FG2.ndf',
    SIZE = 500MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 250MB
)
TO FILEGROUP NewFG;
GO

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID)
WITH (DROP_EXISTING = ON) ON [NewFG]
GO

I am pretty sure that truncating is the most efficient way.  However, you may want to re-index the table.  Hope this helps!


Replace space (" ") with no space ("") in one column I have a table like this:

ID | Propinsi | Kota |
_________________________
 1 | Aceh     | Denpasar
 2 | Aceh     | Banda Aceh
 3 | Sumatera | Asahan

This table has many rows. The problem is I want to replace the space before the text in column Kota for all rows like this:

ID | Propinsi | Kota |
_________________________
 1 | Aceh     |Denpasar
 2 | Aceh     |Banda Aceh
 3 | Sumatera |Asahan

I searched Google, the function replace in MySQL only affects one row:

SELECT REPLACE(string_column, 'search', 'replace') as Kota

Can someone fix my problem?

Try this:

To show without space:

select trim(kota) from yourtable

To change your data:

update yourtable set kota = trim(kota);

TRIM function is different to REPLACE. REPLACE substitutes all occurrences of a string; TRIM removes only the spaces at the start and end of your string.

If you want to remove only from the start you can use LTRIM instead. For the end only you can use RTRIM.

UPDATE yourtable SET kota = TRIM(BOTH ' ' from kota);


Migration MySQL to Cloud Environment replication I'm having 5 MySQL database server , these  5 server are used for e-business web-server. Now I Would like to move the MySQL database to cloud environment for database continuous running. Recently we face hardware failure (misc), in future we need to avoid any failure the database should continuously up and running that is the main objective

Guys could you suggest best way to proceed this scenario ?

we already done the master & master replication in locally , so can any one recommended to which cloud is best for my query like (AWS , Openstack,SoftlayerCloud ) on it.


MySQL using too much CPU I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:

CentOS 5.9
24gb Ram
8 Core CPU
50gb FusionIO disk for /var/lib/mysql
MySQL 5.5.23

top - 18:40:27 up 14 days,  4:43,  1 user,  load average: 19.72, 22.62, 24.04

Tasks: 183 total,   3 running, 180 sleeping,   0 stopped,   0 zombie

Cpu(s): 69.9%us,  0.4%sy,  0.0%ni, 29.2%id,  0.0%wa,  0.1%hi,  0.4%si,  0.0%st

Mem:  24685224k total, 20172096k used,  4513128k free,   343420k buffers

Swap:  2007284k total,        0k used,  2007284k free,   729004k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

5446 mysql     15   0 18.4g  17g 6176 R 765.8 76.2 114437:38 mysqld

I'm currently using 43gb of that 50gb FusionIO slice. MySQL averages around 700 QPS and 75-90% CPU usage. Here's my my.cnf file:

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#innodb
#innodb_log_file_size = 256M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=4
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400

skip-name-resolve
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10

event_scheduler=ON

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

can you guys recommend any config changes I should make to MySQL or CentOS that can greatly reduce my CPU usage? Or for that matter any other resource improvements?

You need to unleash two things

innodb_read_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_write_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_thread_concurrency (Default is 0. Since you don't have it, 0 is just fine)

I have this discussed this over the years. Please see some of my earlier posts on getting InnoDB to engage more CPU/Core activity.

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?

Give it a Try !!!
UPDATE 2013-06-20 02:39 EDT
Your last comment

Hey Rolando, I added read_io and write_io at 8 and read through your other posts but it doesn't seem to be bringing down the CPU usage. I even dropped the 4 buffer pool instances down to 1 in hopes that it would have better performance, but still no go. Do you have any more suggestions that I could implement that will help drop CPU usage?

I would suggest raising innodb_buffer_pool_instances to 8 to match the number of cores. The MySQL Documentation suggests 1G per buffer pool instance. Then, you would raise to 16 (since you have innodb_buffer_pool_size at 16G). Try 8 first, then 16.
Even with this, I have some rather distressing news: Here is a Press Release From FusionIO from 2 years ago. Paragraph 2 says:

The StatSoft white paper concluded that Fusion ioDrives significantly increased the I/O performance in the STATISTICA suite of analytics software products and solutions, greatly increasing CPU utilization and efficiency. With ioMemory, StatSoft achieved 300 and 500 percent data performance and latency reduction improvements when compared to legacy disk-based storage. With the increased I/O performance enabled by the Fusion ioDrives, CPU utilization increased to 90 percent in tests of large data sets, versus the 32 percent CPU utilization observed with the disk-based technology.

If you were using RAID 10 SAS or even SSDs for /var/lib/mysql, InnoDB would have its day with the CPUs. However, from the looks of this Press Release, InnoDB is competing with FusionIO for CPU Utilization.
SUGGESTION
Find out what the IOPs are for the FusionIO disk and set innodb_io_capacity to that number. The default value for innodb_io_capacity is 200. If the IOPs are greater than 200, then please set it. If the IOPs are in the 1000's, then InnoDB has a chance to be on a level playing field with the FusionIO.
Give it a Try !!!

I had the same issue. Some of the tables have MyISAM while others have InnoDB. Just fixing this we are lower the CPU usage from 98% to 26%. We will try to optimise some of the SQL. Hopefully, this will bring more joy.


Can I install Oracle 12c and NOT use pluggable databases? I've just installed Oracle 12c.   I did not select any of the fancy new options, I just wanted an Oracle instance.   I'm fairly new to Oracle but I was used to 11.2.0.4.

Every command I try now fails and it's always something like "Cannot do [x] on pluggable database", etc.

I just want a normal oracle instance and I want to be able to create users and import my database but every turn I take has some frustrating error having to do with pluggable databases.  I don't care at all about them, and I specifically tried to avoid any install option that looked like it would make a "pluggable" database but apparently it still did.

Is there any way I can just keep life the same in 12c or am I stuck with this entirely clumsy pluggable paradigm?
Thanks very much.

Yes it is possible to use single tenant..
here you go --> Single Tenant Configuration

Yes, it is. Just create a Non-CDB (the old architecture) database, but keep in mind this architecture will be deprecated in the future.


How to represent foreign key in an ER diagram? Suppose I have a 'Transactions' table which has a column 'Customer ID' (Foreign Key) and a Customer Table having 'ID' (Primary key). How do I show the relation between the two tables and showing that the 'Customer ID' is the foreign key of 'Transactions' Table which is the primary key in the 'Customer' table?

I googled this question and also checked this forum for my query but couldn't find an exact example with a diagram addressing my question.

Please explain me, if possible, with a diagram.

ER Diagrams were originally used only to represent the ER model.  The ER model does not use foreign keys to represent relationships.  It uses lines between boxes.  The lines have some kind of indicator for cardinality at either end or both ends.  Sometimes, a relationship will be indicated separately by a diamond.

Today, more than half of the ER diagrams floating around are really diagrams of a relational model, and not of an ER model.  A relational model has the foreign keys included in the tables, and these serve to implement the relationships which the ER model identifies.  And a relational model will have an extra table, often called a "junction table" between two entity tables that are linked by a many-to-many relationship.  This junction table contains two or more foreign keys.

There are many ways to represent a relational model. Perhaps the simplest is the "Relationship Diagram" that MS Access can produce from a completed database.  This will be fairly complete, if the database builder has identified the foreign keys.

There are many tools that are more sophisticated than MS Access for making diagrams on a larger scale.  Some of these are used before building the database.  Some are used after.

This page, by LucidCharts has a nice write up on how ERD should look.

Under Physical ERD Symbols, it shows how to represent keys.

[https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning]


Logical operators OR AND in condition and order of conditions in WHERE Let's examine these two statements:

IF (CONDITION 1) OR (CONDITION 2)
...

IF (CONDITION 3) AND (CONDITION 4)
...

If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will CONDITION 4 be checked?

What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?

ADDED:

Thank to Jack for link, surprise from t-sql code:

IF  1/0 = 1 OR 1 = 1
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

IF  1/0 = 1 AND 1 = 0
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

There is not raise a Divide by zero exception in this case.

CONCLUSION:

If C++/C#/VB has short-circuiting why can't SQL Server have it?

  To truly answer this let's take a look at how both work with
  conditions. C++/C#/VB all have short circuiting defined in the
  language specifications to speed up code execution. Why bother
  evaluating  N OR conditions when the first one is already true or M
  AND conditions when the first one is already false.

  We as developers have to be aware that SQL Server works differently.
  It is a cost based system. To get the optimal execution plan for our
  query the query processor has to evaluate every where condition and
  assign it a cost. These costs are then evaluated as a whole to form a
  threshold that must be lower than the defined threshold SQL Server has
  for a good plan. If the cost is lower than the defined threshold the
  plan is used, if not the whole process is repeated again with a
  different mix of condition costs. Cost here is either a scan or a seek
  or a merge join or a hash join etc... Because of this the
  short-circuiting as is available in C++/C#/VB simply isn't possible.
  You might think that forcing use of index on a column counts as short
  circuiting but it doesn't. It only forces the use of that index and
  with that shortens the list of possible execution plans. The system is
  still cost based.

  As a developer you must be aware that SQL Server does not do
  short-circuiting like it is done in other programming languages and
  there's nothing you can do to force it to.

There's no guarantee in SQL Server if or in which order the statements will be processed in a WHERE clause. The single expression that allows statement short-circuiting is CASE-WHEN. The following is from an answer I posted on Stackoverflow:
How SQL Server short-circuits WHERE condition evaluation

It does when it feels like it, but not in the way you immediately think of.
As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

For further details check the first link in the above blog entry, which is leading to another blog:
Does SQL Server Short-Circuit?

The final verdict?  Well, I don't really have one yet, but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression.  With standard boolean expressions, the optimizer will move things around as it sees fit based on the tables, indexes and data you are querying.

1)--OR ( any one or both conditions will be TRUE)

if condition 1 is TRUE then condition 2 will also checked it can be either TRUE or FALSE

--AND ( both conditions must be TRUE)

if condition 1 is FALSE then condition 2 will not be checked


sql server transactional - replication - the internal procedures often deadlock with user processes Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example:

  Deadlock of the replication procedure frame procname=Bocss2.dbo.sp_MSupd_dbotblBOrderItem line=79 stmtstart=8684 stmtend=14714 sqlhandle=0x03002a004d01df507b1245016ba3000001000000000000000000000000000000000000000000000000000000

With the following query:

SELECT 'INSERT INTO @TempTable1 (OrderNo, OrderSeqNo, OrderItemDate)
VALUES (LTRIM(''',strBxOrderNo, '''),', sintOrderSeqNo, ', ''',
  CONVERT(VARCHAR(20), sdtmOrderItemStatusUpdated, 113), ''')'
FROM tblBOrderItem
WHERE sintOrderItemStatusId = 4 -- In Picking
AND sdtmOrderItemStatusUpdated BETWEEN GETDATE() -30 AND GETDATE() -1
 -- Give the warehouse a chance to deliver the order
AND decFinalPrice > 0
ORDER BY strItemNo

I don't want to enable the snapshot isolation level - where writes don't block readers - at the cost of maintaining a row version at the tempDB, but I am considering partitioning the big tables that are usually the culprits on the deadlocks.

what stats I could have a look at so that I could guess whether or not this is a good idea?

Partitioning will likely not help, unless the user query in the deadlock can be rewritten in order to let partition elimination kick in.

I would focus on making both operations in the deadlock graph as fast as possible instead. This will involve ad-hoc indexing or rewriting the query in a different form.

If the replication agent is often the deadlock winner, you could consider lowering its deadlock priority, in order to let the user queries run without being killed. However, I would explore this option only as a safety net in case you're not able to reduce deadlocks to a very rare event.

I know this doesn't specifically answer your questions, but in similar situation I found that you can set the priority with regards to deadlocks - i.e. which process is chosen as the victim: https://msdn.microsoft.com/en-us/library/ms186736.aspx

This allows you to control whether the current code is likely to be chosen as victim and therefore give your chosen process a better chance at completion than the 'other' process. I'm not sure how you'd link this in to replication (assuming you want replication to 'win'), but could be useful?

We didn't end up using this approach, we used 'No Lock' where applicable on the other process to relieve the contention. In our environment - it was more important to let the 'other process' complete than worry about Replication needing to reconnect.

PS. Partitioning your tables is well worth doing in my experience, especially if you can move your most active tables onto faster media.


SQL Cluster with different Edition Is it possible to join new node SQL in Failover cluster with different Edition?
As example:

Cluster Failover running based on SQL Enterprise Evaluation Edition add a new node based on SQL Standard Edition

I've never tried this but I'm reliably informed it is 100% not possible. It is certainly not supported by Microsoft, for good reason, so why would you do it?

  The SQL Server edition you are installing must match across all the
  nodes in a SQL Server failover cluster. When you add a new node to an
  existing SQL Server failover cluster, make sure that you specify that
  the edition matches the edition of the existing failover cluster.

This comes from this MSDN link found almost instantly after a quick web search.

Bear in mind there are a few obvious and not-so-obvious functional reasons why you would not want to failover from Enterprise to Standard as some features are not supported in both.

The idea of a clustered environment should be that you maintain integrity of the database environment upon failover, so why compromise that in any way, whether talking about support or features?

This should be possible.

However, do note that some features that are present in Enterprise Editions may not be available in Standard Editions. This can cause severe application issues when a Failover occurs.

I suppose, a Standard edition may be used on an Always-On Secondary Replica where Reporting is offloaded and it does not ideally act as a Failover node. In any case, please test out your solution before taking it forward on Production.


Why do SQL Server row estimates change when I add a join hint? I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query is fairly straightforward, looking something like this:

SELECT t1.id
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id
LEFT OUTER JOIN t4 ON t3.t4_id = t4.id
WHERE t4.id = some_GUID

Playing around with the query, I noticed that when I hint it to use a Merge join for one of the joins, it runs many times faster. This I can understand - Merge join is a better option for the data that is joined, but SQL Server just doesn't estimate it right choosing the Nested Loops.

What I don't fully understand is why does this join hint changes all the estimates for all the plan operators? From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built, so using a hint would not have changed the estimations, but rather explicitly tell SQL Server to use a particular physical join implementation.

What I see, however, is that Merge hint causes all estimations to become pretty much perfect. Why does this happen and are there any common techniques to make query optimizer make a better estimate without a hint - considering that statistics obviously allow for this?

UPD:
anonymized execution plans can be found here:
https://www.dropbox.com/s/hchfuru35qqj89s/merge_join.sqlplan?dl=0 https://www.dropbox.com/s/38sjtv0t7vjjfdp/no_hints_join.sqlplan?dl=0

I checked the stats used by both queries using TF 3604, 9292 and 9204, and those are identical. However indexes which are scanned/seeked differ between the queries.

Besides that, I tried running the query with OPTION (FORCE ORDER) - it runs even faster than using merge join, choosing HASH MATCH for every join.

From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built.

Not exactly. An initial cardinality estimate is derived (after simplifications and other work), which influences the initial join order chosen by the optimizer.

However, subsequent explorations (during cost-based optimization) can, and often do, result in new cardinality estimations being calculated. These later CEs may be more or less 'accurate'. If an under-estimate results, the optimizer may choose a plan that looks cheaper, but in fact runs for much longer.

In general, there is no guarantee that cardinality estimations for semantically identical subtrees will produce the same results. It is a statistical process, after all, and some operations have deeper CE support than others.

In your case, there appears to be another factor - the optimizer introduces (or moves around) a Top, which sets a row goal on the subtree beneath it:

If you were to enable trace flag 4138 (on 2008 R2 or later), you may find the estimates more in-line with expectations, or perhaps even that the optimizer would no longer choose nested loops.

  What I see, however, is that Merge hint causes all estimations to become pretty much perfect.

There is an element of luck involved here. People tend to write queries, or at least the joins, in the order they expect them to be performed physically. Using a join hint comes with an implied FORCE ORDER, thereby fixing the join order to match the textual form, and switching off many optimizer exploration rules that can lead to cardinality re-estimation.

  Besides that, I tried running the query with OPTION (FORCE ORDER) - it runs even faster than using merge join, choosing HASH MATCH for every join.

This is the same as hinting a join, but does not restrict the choice of physical join operator. Again, if you happened to write the query join order in a logical way, it is quite likely you will get a reasonable plan. Of course, you miss out on much of the optimizer's abilities this way, which may not produce optimal results in more general situations.

You will probably not want to use FORCE ORDER very often because it is an extremely powerful hint (directive) that has wider effects than simple forcing the order of joins; for example, it prevents the optimizer moving aggregates around and introducing partial aggregations. I very much advise against using this hint except in very exceptional circumstances, and by truly expert tuners.

A detailed analysis would require more time than I have right now, and access to a statistics-only copy of the database.

The where negates the left
Why make it hard on the optimizer?
At 3 or more joins the optimizer will TEND to go defensive and into loop joins as that protects memory
An or condition in the join it will also tend to go into a loop join - do I have hard evidence it will happen every time - no - still a reality
With multiple joins pull conditions from the where into the join when you can

SELECT t1.id
  FROM t1
  JOIN t2
        ON t1.id = t2.t1_id
  JOIN t3
        ON t2.id = t3.t2_id
  JOIN t4
        ON t3.t4_id = t4.id
       AND t4.id = some_GUID

Or even better yet - I bet this will meet or beat your hints or force

SELECT t1.id
  FROM t1
  JOIN t2
        ON t1.id = t2.t1_id
  JOIN t3
        ON t2.id = t3.t2_id
       AND t3.t4_id = some_GUID

The problem with hints is they are for data in a specific state.  Write a clean query and let the optimizer do its job.  Some times it it just needs more statistics to do the right thing but then it will lock in.

Why different estimates.  A different plans.  Start with queries that give the optimizer a fighting chance.


Is it possible to speed up this PARTITION BY query? I've got a timeserie table. Each datapoint has also a statut field which
indicates its providing source. I'd like to request last available datapoint
as of date T but there could be several results, each with a different statut.

Therefore, I'm using a "statut preference list" to retrieve datapoint in
accordance with providers priority.

Here's the query : (in this example, my preferred ordered statuts would be
10 then 9 then 1)

DECLARE @MyStatuts TABLE (StatutOrder tinyint, StatutValue tinyint)
INSERT INTO @MyStatuts VALUES(1, 10)
INSERT INTO @MyStatuts VALUES(2, 9)
INSERT INTO @MyStatuts VALUES(3, 1)

SELECT TOP 1 Id, Date, Statut, Value
FROM (
    SELECT data.Id, data.Date, data.Statut, data.Value, s.StatutOrder,
           MIN(s.StatutOrder)
             OVER(PARTITION BY data.Date) As MinStatutOrder
    FROM [DATA].[TimeSerie] as data
      JOIN @MyStatuts as s on data.Statut = s.StatutValue
    WHERE Id = 'timeserieid' AND Date <= '24/04/2014'
    ) AS T
WHERE T.StatutOrder = T.MinStatutOrder
ORDER BY T.Date DESC

Do you have any ideas how I can speed that up ? I already used Index on [TimeSerie] table...

Here's table code :

CREATE TABLE [DATA].[TimeSerie](
[Id] [varchar](25) NOT NULL,
[Date] [date] NOT NULL,
[Statut] [tinyint] NOT NULL,
[Value] [decimal](19, 8) NOT NULL,
CONSTRAINT [PK_Price] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[Date] ASC,
[Statut] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Link to the execution plan

I think your query uses only the rows where the date is the maximum so it is equivalent to this:

SELECT TOP (1) data.Id, data.Date, data.Statut, data.Value
FROM [DATA].[TimeSerie] AS data
  JOIN @MyStatuts AS s ON data.Statut = s.StatutValue
WHERE data.Id = 'timeserieid'
  AND data.Date = ( SELECT MAX(m.Date)
                    FROM [DATA].[TimeSerie] AS m
                    WHERE m.Id = 'timeserieid'
                      AND m.Date <= '20140424'
                  )
ORDER BY s.StatutOrder ASC ;

If you use

SELECT RowNum,Security, Date, Statut, Value

instead of

(SELECT Security, Date, Statut, Value)
WHERE RowNum=1

it will slow down the query by 60%. Why I can't get the reason.


mismatch between server schema content and exported sql rows I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data.

I am trying to figure out, when I export my table from phpMyAdmin the exported sql file structure when opened does not match the schema seen in the admin panel. By that I mean the row sequence in the sql file are jumbled up.

I don't have any foreign key constraints set up or any other operations on the table.

Any steering and knowledge as to why this might be the case would be appreciated.

The issue disappeared when I changed the engine from MyISAM to InnoDB. I understand that InnoDB has row locking.


Error while exporting data to flat file from database using sqlplus in UNIX Trying to export data from a table to .dat file in a unix script using the SPOOL command.

However on execution only ~17k data is exported to file out of 91K data with an error in end of the file : ORA-08103 Object do not exist, though the objects exists in the database.

When I tried to export the data in csv, it exported only ~11k data with same error in the file.
Exporting almost ~35 columns from the table.

spool ${DataDirectory}/output.dat;

select * from < Table_name >;

What could be the reason for not exporting complete data set and throwing a error even if object exist?

Attached is the screenshot of the script.

ORA-08103 object no longer exists

means you were querying a segment that has since been (re)moved.  The most common examples of this are:

Someone truncated the table
Someone did 'alter table move'
Someone did 'exchange partition'

all of which move a segments contents to a new place on disk, hence the data you were scanning "no longer exists"

When you are spooling from SQL*Plus for making script that time always execute following commands before executing spool

set heading off

set feedback off

set pagesize 0

spool file_name

Because these commands will build clean spool file for using any dynamic script. In your issue, there will be some inputs are coming from spool script which is resulting to throw error ORA-08103


SSMS Permission Denied, Login with Windows Authentication My SSMS(SQL Server Management Studio) version 16.4, Windows 7. First install is normal use, sometimes i reinstalling my Windows and just now i install SSMS again, and got this problem.
I cannot do anything except view.

Object Explorer & Registered Servers Display

BUILTIN\Users (ServerRole: Public) > using windows authentication
sa (ServerRole: Public, Sysadmin) > SQL Server Authentication (with default password)

I cannot create any database, restore or anything that change the content of my server. Also i have try to Add new login, and i don't have permission to create new login. I run the SSMS as Administrator, but i still cannot do anything.

Anyone know how to add new logins from out of application. Maybe i can connect to Server with SQL Server Authentication that have all permission that required. Or any way to solve this permission problem.

Thanks

Attach Database -> Add

Restore Database

Try something to do

Error when add ServerRole to my BUILTIN\Users

Connection Properties if needed.

You are just a member of BUILTIN/Users when connecting to SQL Server, your USER\Administrator is not a member of sysadmin role, so you have no server permissions. Try to execute

select *
from sys.fn_my_permissions(null, 'server');

and all you see will be CONNECT SQL + VIEW ANY DATABASE.
Of course you cannot use DAC, you are NOT sysadmin to be able to do it.

What you should do instead is launch your server in single user mode, and as you are local admin, you'll be able to log in as sysadmin.
After that you should add yourself to sysadmin role.

Here are the steps to perform: Connect to SQL Server When System Administrators Are Locked Out

query keyin

EXEC sp_addsrvrolemember 'Domain\User' 'sysadmin';

It works for me.


No of rows to be displayed I have 100 records. I want to display only 10 rows of that record default or by specifying the number by using procedure

You can specify the number of records to return by using TOP

SELECT TOP 10 * FROM table

CREATE PROCEDURE sp_return_top_n_rows
             BEGIN
                     SELECT  DISTINCT *
                      FROM    table
                       LIMIT 10

              END


How does paging work with ROW_NUMBER in SQL Server? I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee selects all records in the Employee table (to create the  MyRowNumber values).

I think, this causes selection of all records in the Employee table.

Does it really work so? Or is SQL Server optimized to select only the 5 records from the original Employee table too?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize

An alternative to test might be:

;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize
ORDER BY ...;

Yes, you hit the table twice, but in the CTE where you scan the whole table you are only grabbing the key, not ALL of the data. But you really should look at this article:

http://www.sqlservercentral.com/articles/T-SQL/66030/

And the follow-up discussion:

http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx

In SQL Server 2012 of course you can use the new OFFSET / FETCH NEXT syntax:

;WITH x AS
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...;

I also blogged about this in more detail here:

SQL Server v.Next (Denali) : Using the OFFSET clause (paging)
Pagination with OFFSET / FETCH : A better way

I don't think it evaluates to return all rows in the original table.
SQL server optimizes.
Otherwise it will take a huge amount of time to select a million entries.
I am currently using this and it is much faster than selecting all rows.
So, sure does not get all the rows.
It is however slower than just fetching the first five rows, probably due to time taken in ordering


Individual SQL account per user? I am designing a web application that will have probably around ~1000 users.  The website is facing the internet but it can only be used by partners (i.e. the general public cannot just "sign up an account").  Account maintenance will be handled by our own IT support.

I am considering having each individual user to have their own SQL login associated.  This way I do not need to store their salted/hashed password - SQL server will handle the authentication.  This also allow us to use SQL Server based audit and logging tool.

Is there any disadvantage doing that?

Yes, plenty :)

You will have to setup encryption from the application server to the database server as the password is passed in clear text.
All login requests from the internet will result in an expensive authentication request to the database server.
You are not able to use connection pooling which will result in having a single connection for each user accessing the database.

The only strong reason to install separate instances on same hardware is if you have very very stringent security isolation requirements. Otherwise is always better to have only one instance. One single instance can better optimize all hardware resources for the load you're facing. Multiple instances don't communicate to each other and they overlap the memory, I/O and CPU load resulting in worse performance.

Also a single instance is easier to administer, monitor and troubleshoot rather than separate instances.


Mariadb default logs location & --log-error I am using mariadb and I to investigate some issue I wanted to check the logs. To my surprise, log file is not generated for mariadb.

I suspect this cannot be the case so I am doubting my search skills.

MariaDB [(none)]> show variables like 'log_error'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.00 sec)

I have added the entry in my.cnf still above field is coming to be empty.

[root@cslcodev11-oem ~]# cat /etc/my.cnf
[mysqld]
!includedir /etc/mysqld/conf.d
datadir=/mnt/mgmt/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/mnt/mgmt/var/lib/mysql_tmp
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
### TRT-3229 #####
sync_binlog=1
innodb_flush_method=O_DIRECT
innodb_support_xa = 1
myisam_repair_threads = 2
myisam_recover_options = FORCE
###################
innodb_file_per_table=1
innodb_log_buffer_size = 8M
table_open_cache=256
max_heap_table_size=256M
### TRT-4685 ###
max_connections=500
################
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mariadb/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@cslcodev11-oem ~]#

So, I want to know do we have any default location where these logs are getting generated, if the path cannot be read from config files.

Update:

After some investigation, I found this information,

Reference: https://mariadb.com/kb/en/mariadb/error-log/

Activating the Error Log

The error log is active by default. The log-error=filename option determines where the output will be written. If no file name is specified, the log will be written to host-name.err. If no absolute path is specified, the file will be written to the data directory (determined by the value of the datadir system variable).

On Unix systems, if the --log-error option is not used, the errors are written to stderr (usually, the command line).

On Windows, if the --console option is specified, and --log-error is not used, the errors are written to the console. If --log-error is present, --console is ignored.

So now I understand that due to some reason the configuration log_error in my.cnf is not considered.  So as per the suggestion, on Unix system, if --log-error is not used, logs are redirected to stdout.

BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?

Going off the documentation, the error log should be in the default location of /var/lib/mysql and it should be named the default hostname.err, but it's just not there.

In the directory /var/lib/mysql I do have a tc.log, iblogfile0, iblogfile1, aria_log.00000001, and aria_log_control files. But I cannot read any of them, they are encrypted. Anyone know what these are?

  BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?

After running the following command,

cat /etc/systemd/system/mysqld.service | grep "mysql"

we can see that the line that systemd uses to start the process is ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION. The options passed to MariaDB are stored in the $MYSQLD_OPTS variable.

If we also run cat /etc/systemd/system/mysqld.service | grep "MYSQLD_OPTS" we get the following.

MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
Use the [Service] section and Environment="MYSQLD_OPTS=...".
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

The file MY_SPECIAL.conf does not exist on my machine, as find / -name "*MY_SPECIAL*" reveals.

I do have entries for mysqld in journalctl. So I'm thinking that it uses the the systemd system logs by default for us systemd users.

On the MariaDB Knowledge base page for the error-log there is a paragraph that states.

  systemd has its own logging system, and Linux distributions running systemd may log errors there instead. To view the systemd logs, use journalctl -u mariadb.

This answers the question because --log-error would be an option that could be added to MY_SPECIAL.conf file and journalctl is where mariadb logs errors by default on a systemd system.

the variable name for log path is log_error but you have set log-error in your my.cnf and therefore the logs are not generated. if you correct it in your my.cnf to log_error, it should work.


INNER JOIN making COUNT(*) slow I have a very simple query:

SELECT COUNT(*)
FROM messages
INNER JOIN users ON messages.user_id = users.user_id

With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to me). Test carried out on the messages table containing 1,000,000+ rows.

I have I have a primary key set on both tables (message_id and user_id) and a foreign key setup connecting messages.user_id and users.user_id.

The reason for this query is to provide the total number of records for a paging system.

What else can I do to speed up the query?

If the foreign key is not nullable, then there is no reason for the join at all, is there? So why not let SQL Server scan the most efficient index available instead of computing a join that will result in the exact same count?

SELECT COUNT(*) FROM dbo.messages;

That said, you can do this much more efficiently:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.messages')
  AND index_id IN (0,1);

This will be slightly inaccurate due to in-flight transactions, but it also won't block (or be blocked by) any activity against the table, so pick your poison.

You should create an index.
An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX index_name
ON table_name (column_name)

You should only create indexes on columns (and tables) that will be frequently searched against.


Best approch to replicate data from one db to other db on same server I am facing issue of time out and slow data response so I did search on Google and found that I could create a server which replicates main database data to secondary database and both on same server instance; main database used for real time read and write and the Report server will get data from the secondary   database so when someone runs a large report then it will not affect the main database and the application will run with out any issue.

What replication solution I have to use for situation? I am using SQL Server 2012 Standard Edition.

If the data in the reporting database does not have to be real-time, the I suggest you do not implement replication but instead go with log shipping.


do oracle redo logs need to be copied when all other data is copied to a new disks? I have to migrate all data for an Oracle server from  the present SAN to a new SAN.  It is an 11g database in production on a Linux server.  Database software is on local disk but all data (including datafiles, archive logs, control files, etc) are on a SAN via NFS.

The plan is to shut down Oracle before the sysadmin copies over the data to the new SAN.  Since we are using NFS, when I start up Oracle again, the change should be transparent to Oracle.

The sysadmin has been told that the redo logs do not need to be copied.  It seems to me that the redo logs should be archived before the copy.  Should I force a log switch (alter system switch logfile)before the shutdown and then copy the archive logs?  Or will a shutdown trigger the redo logs to be archived?

If you are using ASM, you could even do this online by first adding the new disks to the current diskgroups, rebalance the disk groups and drop the old SAN disks from the diskgroups. During this drop, the first thing that happens is - again - a rebalance action in which the data that is on the dropping disks is moved from those disks to the new disks. A very powerful feature, not sure if it can help you.

If it all has to be done using FS copies, it still can be done online, if you are running archive log mode. Since 11g RMAN can clone from an active database, also very powerful.

FWIW: some applications check the controlfile and complain if it is changed. This is part of a check for licenses.

Short answer for you question, you need the online redologfiles. The archives are only needed for recovery operations so they should remain accessible, not perse on the new SAN. A regular backup would be ok. Copy the online redo logfiles and use RMAN for the copy of the database since it handles everything that needs to be done in a good way.

The REDO logs should be copied.

Shutdown doesn't trigger a logfile switch.

If you are changing the paths for the files, then using the "create controlfile reuse noresetlogs" method is the best. If you do move it without REDO logs, then archivelog current and then use the "create controlfile set resetlogs" method. In general though, moving the redo logs are best.

--EDIT--

Based on what you said, you are mounting these in the exact same path, then do your self a favor and just put all of the files, DATAFILES TEMPFILES CONTROLFILES REDOLOGS, etc in the exact same file path. Nothing else to do and it comes up fine.

-Richard


Assign maximum memory limit for a particular database in sql server In my SQL-Server instance, there are many databases. I want to restrict the memory size of each database (for example 8 GB). When I tried using Resource Governor, there are options like max_memory_percent and min_memory_percent and it is given in percentage value (values from 0 to 100). But I want to give the database size explicitly in GB and it should not go beyond that maximum limit.

Is there any way to restrict the database size or to get alerts if the database size exceeds the limit?

Can I implement this using Resource Governor?

ALTER RESOURCE POOL

There is no way to limit the amount of memory a specific database takes.  SQL Server uses a least used / first evicted from memory policy (basically).

You can assign minimum and maximum memory in MB for an instance from SQL Server Management Studio using inbuilt functionality. The steps are as follows:

Right click on the instance name and click on Properties.
Select Memory page.
Assign minimum and maximum memory in MB.

Please let me know if this is in line with what you intend to do.


Microsoft SQL Server 2008 R2 backup error I'm trying to do a simple backup to an external disk and getting this error:

  Backup failed for Server 'Server-name'

Additional information:

  System.Data.SqlClient.SqlError: The media loaded on "C:\Program Files\Microsoft SQL.." is formatted to tsupport 1 media families, but 2 media families are expected according to the backup device specification.
  (Microsoft.SqlServer.Smo)`

Backup type: Full

Why am I getting this error and why is the message pointing to C:\Program Files.. if the destination file is an external device?

This error means that you "striped" your backup across multiple files, and you're only providing one of the files when you're trying to restore.

If you're backing up using SQL Server Management Studio, make sure that you click "remove" to remove any existing backup destinations before you "add" the file you want to back up to.

To avoid above error, just replace your new backup location with listed backup locations. Means remove all old entry and add your new backup location. It will work.


Is it realistic to expect a database of size over 50 GB (some 300+ million records) to be imported within 3-4 hours on a single server (MySQL)? I have hundreds of millions of rows in a text/csv file (genomics database btw - each record is less than 255 characters long...).

Ideally I'd like to make them searchable since right now my best bet is spiting them (a little help from cygwin!) and reading them one by one as a text file ~500mb from notepad++ (yes...i know...) - so this is very inconvenient and caveman-like approach.

I'd like to use MySQL but maybe others, have budget of up to $500 for Amazon instances when needed - maybe 32gb ram some xeon gold and 200gb hard disk on Amazon can do it? No problem to use up to 10 instances each of which doing concurrent insert/loading.

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram - if I make it to even 50,000 rows/second and then be able to query it with say phpmyadmin in normal time -  I'd be happy. Thanks!

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram

That sounds like taken from my blog posts (or at least those were my numbers and specs): https://jynus.com/dbahire/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/ and https://jynus.com/dbahire/testing-again-load-data-on-mysql-5-6-5-7-8-0-non-ga-and-mariadb-10-0-10-1-and-10-2-non-ga/

As you can see my experience is based on actual tests; but not only laboratory tests like the above, I do those because they help me be ready to make sure my database backups (and recoveries) are generated correctly, as well as they can be performed reliably and fastly, handling daily both logical dumps and snapshots for the half a petabyte of data we store on our MariaDB databases: https://www.slideshare.net/jynus/backing-up-wikipedia-databases

A 50 GB database on a 32GB memory server is a very generous ratio, where 60% of the data could fit in the buffer pool. In that case, thoughput can be optimized greatly, as long as you setup your vm, os and mysql configuration for it (disabling the binary log, increasing the buffer pool and transaction log files, loosening consistency parameters during the import, etc). You will also want the original format to be optimized for easy loading, so you dont waste cpu cycles on parsing or converting the format or other changes, as well as doing it in large transactions as well as on several threads in parallel if possible.

As an example, my production has 1-2TB databases with billions of rows, which I can recover logically in 6-12 hours on a 512GB memory machine, including the many indexes.

Under the above right circumstances, with a mostly in memory database, I would be able to load remotelly in parallel a 50 GB database in around 30 minutes. Less than 1h if the storage is slow. Be careful because the tests asume dedicated resources; a cpu, memory or io limitation can create a bottleneck, leading to higher load times.

if you use Redshift Managed Storage, you will pay around 1/2 of T of space ($12/month) and you can query it with queries coming back in less than 2 sec with no indexes (you really don't need any);

at the same time, you will incur the most pain in uploading the said data into S3 prior to import into Redshift but in any case - this is the way forward;

this releases you from any and all management activities once this is done and you just focus on analytics;


Passing array parameters to a stored procedure I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed.  I can indicate this with a big list of IDs.  I'm trying to avoid the "updates in a loop" pattern, so I'd like to find a more efficient way to send this bag of ID's into a MS SQL Server 2008 stored proc.

Proposal #1 - Table Valued Parameters.  I can define a table type w/ just an ID field and send in a table full of IDs to update.

Proposal #2 - XML parameter (varchar) with OPENXML() in proc body.

Proposal #3 - List parsing.  I'd rather avoid this, if possible, as it seems unwieldy and error-prone.

Any preference among these, or any ideas I've missed?

The best ever articles on this matter are by Erland Sommarskog:

Arrays and Lists in SQL Server 2008 - Using Table-Valued Parameters
Arrays and Lists in SQL Server 2005 and Beyond- When TVPs Do Not Cut it
Arrays and Lists in SQL Server 2000 and Earlier

He covers all options and explains pretty well.

Sorry for the shortness of the answer, but Erland's article on Arrays is like Joe Celko's books on trees and other SQL treats :)

Use this to create "create type table". simple example for user

CREATE TYPE unit_list AS TABLE (
    ItemUnitId int,
    Amount float,
    IsPrimaryUnit bit
);

GO
 CREATE TYPE specification_list AS TABLE (
     ItemSpecificationMasterId int,
    ItemSpecificationMasterValue varchar(255)
);

GO
 declare @units unit_list;
 insert into @units (ItemUnitId, Amount, IsPrimaryUnit)
  values(12,10.50, false), 120,100.50, false), (1200,500.50, true);

 declare @spec specification_list;
  insert into @spec (ItemSpecificationMasterId,temSpecificationMasterValue)
   values (12,'test'), (124,'testing value');

 exec sp_add_item "mytests", false, @units, @spec

//Procedure definition
CREATE PROCEDURE sp_add_item
(
    @Name nvarchar(50),
    @IsProduct bit=false,
    @UnitsArray unit_list READONLY,
    @SpecificationsArray specification_list READONLY
)
AS

BEGIN
    SET NOCOUNT OFF

    print @Name;
    print @IsProduct;
    select * from @UnitsArray;
    select * from @SpecificationsArray;
END


Export sql azure DB as bacpac file without including data for certain tables When I export a sql azure database as data tier application from sql server management studio I get the option to tick off certain tables but then these tables are not included in the resulting file.

I want to export these tables schemas, but ignore their data (import empty tables essentially). For all the other tables I also want the data.

Is it possible to do that?

You can create a perfect copy of a table and the whole schema by doing:
SELECT TOP 0 * INTO new_table FROM old_table

If you have a linked server or a database on the same server that is called, let's say, database_copy you can actually iterate all tables and you will end up having the same number of tables, all with the same schema.
Otherwise, if you can mask the data or encrypt them. But in this case they will still be on the database


Representing a Many-to-Many Relationship Between Students and Departments Suppose I have two tables

Table A=Student

Table B=Department

Table A:

CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
DepartmentId VARCHAR(255),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);

Table B

CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int unique foreign key references Student(StudentId)
);

INSERT INTO dbo.department VALUES (101, 'CSE', 1);
INSERT INTO dbo.department VALUES (102, 'Mech', 2);
INSERT INTO dbo.department VALUES (103, 'Civil', 1);

Now the question is:

Can I insert upper values(Values that i given above insert into table B) in  Table B,
I know most of you will say no.

But suppose  I have requirement to do it how it is possible  without removing unique constraint.

Note: Actually In first table I have DepartmentId=1,
I want that more than one row should insert in table b having studentid =1

(against one Departmentid [In Table A], I can add multiple StudentId [table B] in table B)

Output will be like this:

----------------------------------
ID      DeptName       studentId |
101     CSE               1      |
102     Mech              2      |
103     Civil             1      |
---------------------------------

In Sql not allowed duplicate data in Unique key.
but you can create combination of two column of unique key like
CONSTRAINT UC_Person UNIQUE (ID, StudentId)
This will solve your problem.

CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
DepartmentId VARCHAR(255),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);
select * from student
select * from department
drop table student
drop table department

CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int foreign key references Student(StudentId)
**CONSTRAINT UC_Person UNIQUE (ID,StudentId)**
);

INSERT INTO dbo.department VALUES (101, 'CSE', 1);
INSERT INTO dbo.department VALUES (102, 'Mech', 2);
INSERT INTO dbo.department VALUES (103, 'Civil', 1);

In Sql not allowed duplicate data in Unique key.
but you can create combination of two column of unique key like
**CONSTRAINT UC_Person UNIQUE (ID, StudentId)**
This will solve your problem.

    CREATE TABLE student
    (
    StudentId INT IDENTITY PRIMARY KEY,
    Name VARCHAR(255),
    DepartmentId VARCHAR(255),
    );
    INSERT INTO student VALUES ('nav',1);
    INSERT INTO student VALUES ('Angra',2);
    INSERT INTO student VALUES ('navjot',3);
    select * from student
    select * from department
    drop table student
    drop table department

    CREATE TABLE department
    (
    ID INT PRIMARY KEY,
    DeptName VARCHAR(255),
    studentId int foreign key references Student(StudentId)
    **CONSTRAINT UC_Person UNIQUE (ID,StudentId)**
    );

    INSERT INTO dbo.department VALUES (101, 'CSE', 1);
    INSERT INTO dbo.department VALUES (102, 'Mech', 2);
    INSERT INTO dbo.department VALUES (103, 'Civil', 1);


How to create login and logout trigger for client audit? I need a table to put login and logout information for client not sysadmin,then i can use date to statistic user login/off ration...

Can anyone offer me a simple method to create login and logout trigger for audit specific client not the sysadmin,

Thanks in advance.

You can add a simple IF statement to your LOGON trigger, as such:

USE test;
GO
CREATE TABLE LogonAudit
(
    AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID
                PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserName NVARCHAR(255)
    , LogonDate DATETIME
    , spid INT NOT NULL
);
GO
GRANT INSERT ON Test.dbo.LogonAudit TO public;
GO
CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
    IF SUSER_SNAME() <> 'sa'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid)
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;
GO
ENABLE TRIGGER MyLogonTrigger ON ALL SERVER;

This will only audit logons for users not named 'sa' by virtue of the line IF SUSER_SNAME() <> 'sa'

Alternatively, if you want to ONLY audit the login of a specific user, you could write the trigger like:

CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
    IF SUSER_SNAME() = 'SomeUser'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid)
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;

This will only record when SomeUser logs into the server.

create table
stats$user_log
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/


SQL Server 2014 only using 10 percent of its available memory? Hoping someone can weigh in here. I have a 2014 developer edition box that doesn't seem to be performing well. I currently have 128 GBs of RAM allocated to the box and 120 GBs maximum allocated inside of SQL itself.
When I look at the server itself, Windows recognizes it and so does SQL, but it never seems to jump up above 10 percent. All of the other SQL boxes I've built have memory at least sitting at 90 percent or so.
It's running a QA environment that sees maybe 10 users on it at one given time, but I would expect our memory to be using a lot more than it is currently. Any ideas?

if you what that sql server reservs an amount of ram you have to configure minimum server memory.
In your case the min server memory is zero; then sql server takes only the ram it nees to answer the users' queries.
Change it in this way:
EXEC sp_configure N'min server memory (MB)', MBs_you_like
RECONFIGURE


Workarounds for setting default schema for domain group? I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated (Using SSMA SQL Server Migration Assistant) it added a schema name onto the start of all of the table and procedures. Since then we have made lots of changes to some of the procedures within the database.

My problem now is that the application (.NET web app) is calling the procedures without the schema name prefixed. So when it calls the database it cannot find the procedure as it does not have the schema name at the start.

My client has made it clear that the only authentication that they want to use is Windows authentication. From what research I have done I can see that it is not possible to set a default schema for a domain group of users. and it would not be possible for us to add a user in the database for every user that will use the system from that domain group.

Does anyone have any advice on what I could do or a workaround for the default schema issue for domain users.

Thanks

Taking @Mr.Brownstone's idea, you could definitely try this:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA dbo TRANSFER custom_schema.' + QUOTENAME(name) + ';'
  FROM sys.objects
  WHERE SCHEMA_NAME([schema_id]) = N'custom_schema';

EXEC sys.sp_executesql @sql;

Now, it may be the case that you have foreign keys etc. that will need to be dropped and re-created (and I've shown how to automate that too), or as I mentioned in a comment, you may have explicit references to custom_schema in your existing objects. You may also have duplicate objects (say, dbo.Customers and custom_schema.Customers) that you'll need to handle. But the general idea should be sound.

Another workaround would be to upgrade to SQL Server 2012, where you can set a DEFAULT_SCHEMA for a domain group. I haven't tested this, but this Connect item says it has been made so.

In either case, I still think the app should be fixed eventually. As I've mentioned many times before, you should ALWAYS use an explicit schema prefix when creating or referencing objects.

old post, nevertheless I would like to point out an MS article on multi-tenancy where MS itself states not to prefix
http://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_topic2


Simple model database transaction log full 'CHECKPOINT' I have a SQL Server 2012 SP1 database which has a problem that the transaction log is full. It consists of a single data file and a single log file, both about 800MB.

I had 5GB free disk space, and I've enlarged it to 20GB+, just in case there was a disk space problem.

The database is in SIMPLE recovery model.

I cannot do a full database backup - it fails with

  transaction log is full, with wait_desc of 'CHECKPOINT'

MTA: Note - the wait_desc is CHECKPOINT (someone asked below about the wait desc strings)

I cannot add a log file - it fails with the same error.

MTA: I cannot change the growth model of the one existing log file - it fails with the same error.

MTA: The existing log file has +10% growth.

MTA: I also cannot increase the size of the existing log file - it fails with the same error.

I cannot change the recovery model to full or bulk-logged - it fails with the same error.

I cannot execute a manual CHECKPOINT - it fails with the same error.

DBCC LOGINFO (dbid) shows that there are a couple hundred VLFs, all in status 2.

I'm not using change tracking, mirroring, or transaction log shipping.

What can I do to resolve this?

(As recommended, moved from stackoverflow to dba.stackexchange)

MTA: More tech details here (but no answers at all): http://www.sqlservercentral.com/Forums/Topic1451836-2799-1.aspx

eg, "DBCC OPENTRAN
gives me no open transactions"

It sounds like you've got a huge transaction that has remained open which has done a lot of work.  Run DBCC OPENTRAN on the database and see how long the oldest transaction has been open for.  You'll probably need to kill that transaction (or have the user commit if it's someone in Management Studio).  Then the log will clear automatically.

I had exactly the same problem, on a test system luckily. I tried all the same steps to no avail, but eventually a full restore from backup worked for me. Replication kicked in, but because the log was not yet full, I could expand the log size and allow it to grow whilst replication caught up.

My problem was compounded by a broken replication setup on our development environments that filled the transaction log and then got stuck in this endless loop of checkpoint and log full errors.


News Database Design for a Desktop Application I am making an application that will process a big chunk of information. This information was retrieved using some web crawlers and is about news, containing data such as News Title, URL, Publication Date, Category and Content. The crawled data is in XML format, and I will load that information into my application.

From there, 10 users will process every news and tag the category of the news manually by reading the title. I've used 9 main categories to be used, and for each news the users will decide from 0-5 how much this particular news belongs to each category.

Users will also search for news by title and decide if this news is discussing the same event as another news, or if this news is similar to another news (like a news about a football game at 3 PM and a news about a football game at 5 PM).

I have no problems with making the application itself, I just need some help with the part of how to design one/many tables that can link news that talk about similar events or about the same event, since there can be many many news that can talk about the same event.

So far I've done something like this:

Table News

ID
Title
URL
PublicationDate
NewsContent

Table Category

NewsID
User_ID
Economy
Politics
Present_Day
Sport
Technology
Showbiz
Culture
Region
World

Table User

ID
FirstName
LastName

Each category field in the Category table holds a tinyint data type (I'm using SQL Server) and I've restricted the values the user can input via a check constraint so that it allows values from 0 - 5. I don't know if this is the right approach so far for the database design, and I need to add the table/s that allow to add information about similar news or news that talk about the same event, such as a table Similar_News containing some fields like News_ID, SimilarNews1_ID, SimilarNews2_ID and so on, and must contain the information about which user 'said this', but this sounds like a flawed design to me.

Any help is appreciated, thank you.

Based on how dynamic this sounds like I would suggest looking at a NoSQL data structure. I do not use them so much myself, but looking into something like MongoDB might be worth it. Good luck on your application!


Can Fill Factor be changed on specific tables without index rebuild? I've been digging around without any luck.

SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table?

For example, if it has 400 million records, can we adjust the fill factor and have SQL Server use the new fill factor on all pages that haven't already exceeded that limit (as well as new splits), and then adjust pages that are over limit during index maintenance?

Is it possible to pre-define the fill-factor that an index should be built to during the next index maintenance?

This is not how FILLFACTOR works. It only applies to pages written during a CREATE or REBUILD operations. Pages are always filled as much as possible as they are created or split.

As per the MSDN documentation for CREATE INDEX:

  The FILLFACTOR setting applies only when the index is created or rebuilt.

So, even if you change the FILLFACTOR during a REBUILD (since your Clustered Index already exists), only the existing pages will make use of that value. As new pages are created, and existing pages are split, they will be filled as if the FILLFACTOR were set to 100.

Or, stated another way: the rebuild operation is not your problem here as you will never get new and split pages to use a FILLFACTOR below 100.

Also: No, it is not possible to "pre-define" a change to the FILLFACTOR value that will take affect the next time there is a REBUILD. It can only be specified in the WITH clause of a REBUILD operation. BUT, once you have applied the new value during a rebuild, that new value will be stored in sys.indexes and will be used again for subsequent rebuilds unless it is changed again by someone specifying WITH (FILLFACTOR = x).

Or, again stated another way:

The FILLFACTOR value can be changed only when actually doing a REBUILD (I am not considering a DROP and re-CREATE of an Index to be a "change").
The FILLFACTOR value is used only by CREATE INDEX and ALTER INDEX ... REBUILD (and technically also DBCC DBREINDEX, but that shouldn't be used as it has been deprecated as of SQL Server 2005); FILLFACTOR is not used as rows are inserted or updated as that would defeat the purpose of having this option in the first place.

FILLFACTOR reserves space on existing data pages so that the space can be used (hence reducing the chances of getting page splits); not allowing pages to fill beyond the FILLFACTOR value would permanently reserve that space for no purpose, effectively reducing the size of the data pages, which would actually increase, not decrease, the chances of getting page splits.

Page split operations always move approximately 50% to the new page.

No. The moment you change the fill factor, it will rebuild the table. There is no selective pages that you can change the fill factor to.


What is the maximum number of local variables that can participate in SET operation? I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of all other variables. As a result during creation I get the error:

  Msg 8631, Level 17, State 1, Procedure XXX, Line YYY Internal error:
  Server stack limit has been reached. Please look for potentially deep
  nesting in your query, and try to simplify it.

I figured out that the error is due to the number of variables that I need to use in the SET operation. I can perform the assignment by splitting it in two.

My question is are there some restrictions in this area? I checked, but I did not find any.

We checked the error described in this KB, but this is not our case. We don't use any CASE expressions inside our code. We use that temporary variable to prepare a list of values that have to be replaced using a CLR function. We updated our SQL Server to SP3 CU6 (latest up to date), but we still experience the error.

Msg 8631, Level 17, State 1, Line xxx
Internal error: Server stack limit has been reached.
Please look for potentially deep nesting in your query, and try to simplify it.

This error occurs with long SET or SELECT variable assignment concatenation lists due to the way SQL Server parses and binds this type of statement - as a nested list of two-input concatenations.
For example, SET @V = @W + @X + @Y + @Z is bound into a tree of the form:
ScaOp_Arithmetic x_aopAdd
    ScaOp_Arithmetic x_aopAdd
        ScaOp_Arithmetic x_aopAdd
            ScaOp_Identifier @W
            ScaOp_Identifier @X
        ScaOp_Identifier @Y
    ScaOp_Identifier @Z

Each concatenation element after the first two results in an extra level of nesting in this representation.
The amount of stack space available to SQL Server determines the ultimate limit to this nesting. When the limit is exceeded, an exception is raised internally, which eventually results in the error message shown above. An example process call stack when the error is thrown is shown below:

Repro
DECLARE @SQL varchar(max);

SET @SQL = '
    DECLARE @S integer, @A integer = 1;
    SET @S = @A'; -- Change to SELECT if you like

SET @SQL += REPLICATE(CONVERT(varchar(max), ' + @A'), 3410) +';'; -- Change the number 3410

-- SET @S = @A + @A + @A...
EXECUTE (@SQL);

This is a fundamental limit due to the way multiple concatenations are handled internally. It affects SET and SELECT variable assignment statements equally.
The workaround is to limit the number of concatenations performed in a single statement. This will also typically be more efficient, since compiling deep query trees is resource-intensive.

Using SELECT statements instead of SETs can improve performance and readability, and may get you around the stated error. So instead of:

SET @a = 1
SET @b = 2
SET @c = @e + 2*@d

You can do:

SELECT @a = 1, @b = 2, @c = @e + 2 * @d

And set all three values in one statement.


Products relation structure I'm trying to design a products relations table structure. There is an existing product table that has the following fields:

A product could be related to other product but also to a category or a subcategory. Or even, have the three type or just two type of relations at same time.

At the same time, a product subcategory could have a relation to other subcategory or category also to a product.

The main goal for what this is for is when a product is selected by the user, he can get suggestions to other product related to the one selected.

So whats the best table structure you can suggest to store those relations?

I was thinkin some like this:

But I dont know if thats reliable way.

Thank you in advance!

EDIT:

Maybe I did not explain well. Lets say a product x is related to products in Category Y and/or also related to products in Subcategory Z. But could also be related directly to a single product. There are chances to a product have the three types of relations or only two or one of them.

And the Subcategory field of product could be have the three types of relations too.. Or none of them..

Here the more clear and powerful model for you domain


How do I prevent my clients viewing my database definition when it is installed on their server? I want to give my database to the client to install on their server.

However, I want to deny the client rights to see the tables and stored procedures and also want to restrict that database to run from a single IP address.

How do I stop the user viewing the database tables even though database is present on their server?

We want to give the application to run on their own servers, but we don't want to show the tables and stored procedures. They are also not supposed to make any modifications to the database. Is this possible?

Perhaps can we encrypt database credentials in the web.config file?

Short Answer: you can't.

If the database is being run on the customer's infrastructure, then the customer must have control over their own infrastructure.  Without it, they cannot operate.

If you really don't want them to have that control, then you cannot give them the database.  This is the "Software-as-a-Service" Operating Model wherein you licence the application but don't get to play with the "back-end" of things.

I would suggest that this is not a Technical problem.  It's a Licencing one.

I assume that the licence agreement for your software includes a provision that prevents any Customer from "Reverse Engineering" any part of your product.  That clause should be extended to include your database.

Any "mucking about" with the database should invalidate [at least] your support agreement with that Customer.  If they want to take that Risk, well, that's up to them.

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Therefore unless until you can grant any permission to users for their login they wont be able to access tables, however can only see the databases listed on the server:

You just need to make sure, role assigned should be PUBLIC on the user mapping option of Login properties dialog box for the selected databases.


SQL Server 2008 DB Performance on single disk I have a database in SQL Server 2008 with around 20 GB in size. And it is increasing rapidly.

Somehow I can not add multiple independent harddisks to increase IO performance.

If a add large table in separate file group, will it help to improve performance on single disk?

Or any one has tips to improve performance?

Thank you

First off, gather a performance baseline in order to know what the situation is now. Keep monitoring the performance to see if there is a trend for degeneration. If the performance seems to become an issue, there are a few things to do.

Identify the root cause for lack of performance. Is the server expiring memory pressure, IO waits or high CPU usage? The word on the street is to look at Brent's performance tuning pages for a quick start.

Don't overlook good DB design. Check the DB against missing indexes, outdated statistics and poorly written queries. Any of these issues can eat up all the iron you got and then some.

Edit:

Using multiple file groups can - in certain situations - increase performance. Even Microsoft's documentation claims performance advantages. Alas, in your scenario they do not, as you do not have extra disks.

The idea of file groups is to divide database contents to multiple files. File groups make it possible to locate hot tables to high performance storage. This, requires advanced storage configuration your system is lacking. Partitioning tables can take advantage of storage this way too.

Administration tasks like backup/restore can be more manageable with filegroups. Usually a VLDB (very large database) is needed to get such an advantage. Another a classic example is handling read-only data and separating it to its own filegroup. As the data doesn't change, it can be backed up more seldom than the rest of the database.

If you deploy multiple file groups to single a disk, there is no performance bonus. The reason is that you still got a single disk, a single spindle, single set of read/write heads. In order to access data, the disk has to move heads to proper a location. They can't be at two locations at the same time, so extra file group doesn't increase performance.

You can try with  partition of table and index. This method reduce network traffic and increase database performance and save time and space as well

for some tips for sql server performance you can refer these links

http://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/

http://www.brentozar.com/sql/sql-server-performance-tuning/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/


MySQL COUNT Query taking too long time to execute I have below query that takes 7 secs to run

SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
  LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
    ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
  LEFT JOIN `yuldi`.`categories` AS `Category`
    ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction'

when i run it second time it takes 160 ms. what wrong why it takes so long on 1st run on everytime

EXPLAIN:

| id | select_type | table              | type   | possible_keys                                                                                | key                                          | key_len | ref      | rows  | Extra                    |
|  1 | SIMPLE      | Category           | const  | PRIMARY,UNIQUE_SLUG,index_lug                                                                   | UNIQUE_SLUG                                  | 302     | const    |     1 | Using index              |
|  1 | SIMPLE      | BusinessesCategory | ref    | PRIMARY,fk_businesses_has_categories_categories1_idx,fk_businesses_has_categorie_businesses_idx | fk_businesses_has_categories_categories1_idx | 4       | const    | 49630 | Using where; Using index |
|  1 | SIMPLE      | Business           | eq_ref | PRIMARY                                                                                | PRIMARY                                      | 4       | yuldi.BusinessesCategory.business_id |     1 | Using index              |

There are three aspects you need to rework
ASPECT #1 : THE QUERY
Please look at the Query
SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
    ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
LEFT JOIN `yuldi`.`categories` AS `Category`
    ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction';

You are doing LEFT JOINs. That is not necessary for doing a COUNT. Why ?
If you are counting the number of businesses, you only need to count from one table
SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`;

Since you have a WHERE clause on Category.slug, the JOINs are needed. You should switch from LEFT JOINs to INNER JOINs. That will produce a smaller internal temp table. That temp table will be searched for the slug 'building-construction'.
ASPECT #2 : INDEXES
From the EXPLAIN plan, I see two indexes similarly named

fk_businesses_has_categories_categories1_idx
fk_businesses_has_categories_businesses_idx

You need to look at the columns for them and make sure the column lists are not identical.
You may want to make two indexes
ALTER TABLE BusinessesCategory
    ADD INDEX bus_cat_ndx (business_id,category_id),
    ADD INDEX cat_bus_ndx (category_id,business_id)
;

Having compound indexes could mean less culling through tables for JOIN information.
Keep in mind, this suggestion is a blind one since I do not know the key distribution of your data.
ASPECT #3 : CACHING
Gathering index statistics is the sake of query evaluation does not immediately translate into a slow query. That slowness is manifested by the real culprit : CACHING. The reason your query runs faster the second time around has to do with the data you read the first time. The data you needed was most likely not available in MySQL's caches when you issued the query.
The two main caches for MySQL are the InnoDB Buffer Pool (for queries hitting InnoDB tables) and the MyISAM key cache (for queries hitting MyISAM tables).
InnoDB caches data and index pages, while MyISAM caches only index pages.
When retrieving data for comparison, mysqld tries to determine if the data it needs is in RAM first. There are server status variables to monitor for this:
InnoDB Caching

Innodb_buffer_pool_read_requests : The number of logical read requests.
Innodb_buffer_pool_reads : The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

MyISAM Caching

Key_read_requests : The number of requests to read a key block from the MyISAM key cache.
Key_reads : The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

If your query was slow the first time, it simply means the data you need for the query was not in a cache. This would have been indicated by either your Innodb_buffer_pool_reads or Key_reads incrementing.
The second time, your data and/or index pages for the same query would be in RAM and more available for your queries and queries from others.
I suggest you look over the sizes of your InnoDB Buffer Pool and/or MyISAM key cache.

Nothing is bad on the first time. MySQL just uses the collected statistics to further optimize the query and it makes it much faster from the second run.

With the query

EXPLAIN SELECT ...

you can see the execution strategy of a given query. With repeated EXPLAINs between the executions you can see that significant changes there.


Clean Code - On which side to place the key I am currently modelling a database schema. In it, addresses are used several times, which follow a fixed structure. In our previous systems, the address relation was modelled in such a way that the address contained foreign keys, e.g. those of a user.
Reasoning: In this way, an address can only be attached to one parent. From my point of view, however, this is an absolute chaos because sometimes up to eight foreign keys exist in the address relation, whereby care must be taken that only one is filled.
Wouldn't it make much more sense to place the key on the parent, so that by means of a not-zero constraint it can be ensured that each parent has an address and the address relation is clean? The parent is also rarely/never navigated to from the address side.
Or am I violating any conventions by doing this? In such a case, doesn't cleanliness weigh more heavily than a "convention"? If desired, one could also model the previous requirements additionally using triggers?
Current approach: Constraint to ensure only one FK is set on address.

My approach: address_id as FK on parents.

Yes so your approach / the second approach is generally more favorable and normalized because it leads to less redundant addresses. But you lose the flexibility of a user having multiple addresses this way, because of this one-to-one approach. If that's not a requirement of the logical schema, then your approach makes more sense.
Alternatively to support the normalization and flexibility best of both worlds, you can add one more table called userAddress that is a bridge / linking table between the two, supporting a many-to-many relationship by storing the user_id and address_id fields as pairs of all addresses for each user.
So in short:

Approach 1: More flexibility, but less normalization / more data redundancy is possible
Approach 2: More normalization / less data redundancy, less flexibility
Approach 3 (Bridge Table): Flexibility and normalization so reduced data redundancy

Also to your second question regarding Triggers, they aren't all implemented exactly the same way across all modern RDBMS (because the ANSI-compliant spec for them is very minimal relative to the other features of SQL). So for a generalized answer (since we're not talking about a specific database system in the context of this question), it depends on the database system if you'd be able to replicate the desired behavior via a Trigger. (I see you personally use PostgreSQL, so I believe in that context, then it would be possible with a Trigger.)

1 st approach
create table address (
address_id integer primary key,
-- fields related to address
);
create table company(
company_id integer primary key,
-- fields related to company
)
create table User (
user_id int primary key,
address_id  int foreign key references Address(address_id)
company_id int foreign key references Company(company_id)
)
2nd if you have multiple address types you can map address types  in to a mapping table and have the address_type_key in each table as a foreign key.
create table address_type (
address_type_id integer primary key,
address_type  varchar(100)
-- fields related to address type
);
create table address (
address_id integer primary key,
address_type_id  int foreign key references  address_type(address_type_id),
address_line_1 varchar(100),
address_line_2 varchar(100),
State varchar(50),
country varchar(50),
postalCode varchar(10)
-- fields related to address
);
create table company(
company_id integer primary key,
address_type_key  int foreign key references Address_type (address_type_id),
address_key int  foreign key References address(address_id)
-- fields related to company
)
create table User (
user_id int primary key,
address_type_key  int foreign key references Address_type (address_type_id),
address_key  int foreign key references Address(address_id)
company_id int foreign key references Company(company_id)
)


MySQL- Best practices for using rollback What is the best practice / recommended way of using ROLLBACK operation, if any DML operation fails ?

Any ROLLBACK, COMMIT mechanism ?

FYI: I'm using InnoDB tables which complies with Atomicity.

There are two ways not to commit a transaction

Explicit ROLLBACK
Close a connection w/o COMMIT

Both of them are fine to use depending on what fits better the app logic.

When to rollback explicitly?

If you want to reuse a connection. If you want to make transactions as short as possible (a good thing to aim for).

When to close the connect w/o commit?

If a transaction is one-time task, and you'd like to simplify error handling.

Few things to consider.

Do not keep uncommitted transactions for long time.  Often that happens when the app opens a trx, goes to do something else, comes back later to complete and commit the transaction. This really hurts. It leads to high CPU usage and kills performance.
ROLLBACK usually goes into except ... block (or whatever applies to the specific language). But ROLLBACK itself may fail, so make sure to handle exceptions correctly to avoid "Failed to rollback a transaction" errors.
ROLLBACK often takes more time than you'd expect. If transaction was large, the rollback takes more time than all the previous statements in the transaction. Make the transactions as short as possible.

Rollback is not for any DML fails specifically, but it can used in situations like :

Data might be partially inserted due to transient state conflict/error.
Mistakenly injected data in your table.
Using Saving point + Rollback is to rollback if the data is not necessary for future from that specific (save) points.


Temporary files on RAM on PXC? I manage a PXC 5.6 cluster and noticed that the tmpdir variable in /etc/my.cnf isn't set.

I was thinking to set it to /tmp (which is tmpfs) to explicitly make the cluster store temporary files in RAM.  Without this setting, MySQL could choose /var/tmp or /usr/tmp (which, on my system, are on-disk) to store temporary files.

1) Is there any drawback to do this on PXC? I ask because on a MySQL replication slave one must not use a RAM disk to store temporary files, since the slave will need some of the files when the machine restarts.

2) Is this really going to improve performances, or it looks like a unnecessary twiddling? The InnoDB buffer size is already large enough, and 95% of temporary tables are stocked in memory.

It is not advisable to use RAM for temporary directory. At time files can grow large enough to outflow RAM and this would be true even with plain MySQL. RAM is best managed by InnoDB buffer pool. You can setup a tmpfs that should be good enough with decent performance.