Is there a way to backup a mysql database without having to run mysqldump? Is there a way to run a backup of a mysql database using sql? We have scripts which run updates against our databases. They only have mysql user credentials, not system login credentials.

Is there anyway to run backups of mysql via a mysql connection instead?

Is there a way to run a backup of a mysql database using sql?

Short answer: No.

  ... scripts ... only have mysql user credentials, not system login credentials.

I'm pretty sure it's possible to run the mysqldump utility against a remote database.

However, I would have to ask "Why?"

Even if you could take a backup, what would you hope to do with it?

It seems to me that you don't have the tools or the credentials necessary to take proper backups (or to restore a database, which pretty much has to be done on the machine itself).  As such, I would guess that someone else is responsible for keeping these databases up and running, recoverable and well-tuned (at least I hope so, or your organisation is in Big Trouble).

Perhaps you should discuss options with them?

You can try using the built in OS backup software. Been a while, but I think I used to do it with Windows backup many years ago


How can I select rows from a hierarchical query with the lowest level? I have a hierarchical query in Oracle 11gR2 that returns something like this:

Parent (Level 1)

Child (Level 2)

Grandchild (Level 3)

Child (Level 2)

Grandchild (Level 3)
Grandchild (Level 3)

Child (Level 2)

The query I would like to write should get all the rows matching some predicate, for the minimum level; i.e. nearest the parent. For example, if one of the child rows matches the predicate, it should return just that row, irrespective of whether any grandchild rows match. If multiple child rows match, it should return all of them, again irrespective of grandchild rows. If no child rows match, it should return any grandchild rows that match, etc. (In the real system I have a lot more than three levels, and lots more rows per level.)

I assume this is possible with analytic functions, but I'm not sure which one to use, or how to integrate it into my query. I've seen similar problems solved using min (level) keep (dense_rank last order by level), but that doesn't seem to do quite what I want.

If you have an hierarchical query that produces the whole tree under the root node, that also has a level column computed, you can wrap it in a derived table or cte and use the window aggregate:

WITH query AS
  ( SELECT <columns list>, level
  -- your query here
  ) ,
cte AS
  ( SELECT <columns list>, level,
           MIN(the_level) OVER () AS min_level
    FROM query
    WHERE <conditions>
  )
SELECT *
FROM cte
WHERE min_level = level ;

If your hierarchy is just two levels deep, I think this way is short and quick:

SELECT p.* FROM ProductCategory p
LEFT OUTER JOIN ProductCategory c
ON p.ProductCategoryID = c.ProductCategoryParentID
WHERE c.ProductCategoryParentID IS NULL

Hope this helps.


Can view adapt to implicitly reference user's default schema? A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and maintenance can be tricky.

I want to consolidate all these into a single database structure (not a single database - we aren't doing multi-tenancy) to enable upgrades to be applied in a much more uniform fashion. I'm still at the proof of concept stage, but the route I was going down would be to have the standard objects stay in the schema they currently exist in (mostly dbo) and have the custom objects reside in a schema for each client.

For example, I could have dbo.users and client1.users which has some additional columns. If I set the default schema for the client to be "client1" then the following query

SELECT * FROM users

will return data from the client1 schema or the dbo schema depending on which login is connected. This is absolutely perfect for what I'm trying to achieve.

The problem I'm running into is with Views. I have many views which are in the dbo schema and refer to the Users table. No matter which user I connect to the database as, these views always select from dbo.users.

So I'm guessing the question I have is: Can I prefix the tables in the view with some variable like "DEFAULT"? e.g.

SELECT u.username, u.email, a.level
FROM DEFAULT.users u INNER JOIN DEFAULT.accessLevels a ON u.accessID = a.accessID

If this isn't possible and I'm totally barking up the wrong tree, do you have any suggestions as to how I can achieve what I'm setting out to do?

Many thanks.

Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.

With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you hard-code) if a match is not found or if you are using Windows groups for example (which prior to SQL Server 2012 do not support default schema):

CREATE PROCEDURE dbo.whatever_procedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX), @s SYSNAME;

  SELECT @s = d.default_schema_name
    FROM sys.database_principals AS d
    LEFT OUTER JOIN sys.server_principals AS s
    ON d.[sid] = s.[sid]
    WHERE SUSER_SNAME() IN (d.name, s.name);

  SELECT @s = COALESCE(@s, N'dbo');

  SET @sql = N'SELECT x FROM ' + QUOTENAME(@s)
    + '.whatever_view_name;';

  EXEC sp_executesql @sql;
END
GO

You can test this:

USE tempdb;
GO
CREATE SCHEMA foo;
GO
CREATE LOGIN flubat WITH PASSWORD='flubat',CHECK_POLICY = OFF;
GO
CREATE USER flubat FROM LOGIN flubat WITH DEFAULT_SCHEMA = foo;
GO
GRANT SELECT ON SCHEMA::foo TO flubat;
GO
CREATE VIEW dbo.whatever_view_name AS SELECT x = 'dbo';
GO
CREATE VIEW foo.whatever_view_name AS SELECT x = 'foo';
GO
-- create the above procedure
GO
GRANT EXEC ON dbo.whatever_procedure TO flubat;
GO

And then execute it as yourself and also as flubat:

EXEC dbo.whatever_procedure;
GO
EXECUTE AS USER = N'flubat';
GO
EXEC dbo.whatever_procedure;
GO
REVERT;

Results:

x
----
dbo

x
----
foo

But like I said, this is potentially fragile, and I would lean toward either creating a view per schema, or changing the design altogether.

Nasty little hacks abound here.

Try this one:

create procedure mv_view ( @user as varchar,@view as varchar )
as
declare @sqlstr  nvarchar;
begin
select @sqlstr = 'select * from ' + 'user.' + @view +';';
sp_executesql @statement = @sqlstr;
end;


I want a select to give me id's that are not in a consecutively order I have an Oracle db with a table like this:

+----+-------+
| ID | INDEX |
+----+-------+
| 1  |   0   |
| 1  |   1   |
| 2  |   4   |
| 2  |   5   |
| 2  |   6   |
| 3  |   0   |
| 3  |   1   |
| 3  |   5   |
| 3  |   6   |
| 4  |   0   |
| 4  |   1   |
| 4  |   2   |
+----+-------+

I'd want a select to give me all the ID's with INDEX that does not start with 0 or are not in the correct order. From my example I'd want to get 2,3.
THANKS!

create table index_test(id number, idx number);

   insert into index_test (id, idx) values (1,0);
   insert into index_test (id, idx) values (1,1);
   insert into index_test (id, idx) values (1,2);
   insert into index_test (id, idx) values (1,3);
   insert into index_test (id, idx) values (1,4);
   insert into index_test (id, idx) values (2,0);
   insert into index_test (id, idx) values (2,1);
   insert into index_test (id, idx) values (2,2);
   insert into index_test (id, idx) values (2,4);
   insert into index_test (id, idx) values (2,5);
   insert into index_test (id, idx) values (3,1);
   insert into index_test (id, idx) values (3,2);
   insert into index_test (id, idx) values (3,3);
   insert into index_test (id, idx) values (3,4);
   insert into index_test (id, idx) values (3,5);
   insert into index_test (id, idx) values (3,6);

select id from index_test group by id having max(idx)+1!=count(*) or min(idx)!=0;

DECLARE @Line TABLE(Id INT)

INSERT INTO @Line (Id) VALUES (1)
INSERT INTO @Line (Id) VALUES (2)
INSERT INTO @Line (Id) VALUES (3)
INSERT INTO @Line (Id) VALUES (4)
INSERT INTO @Line (Id) VALUES (5)
INSERT INTO @Line (Id) VALUES (7)
INSERT INTO @Line (Id) VALUES (9)

;WITH CTE as
(
SELECT ROW_NUMBER() OVER    (ORDER BY Id) as RowId, Id FROM @Line
)
SELECT * FROM CTE where RowId <> Id

Hope it helps,

If its repeating then you can use rank or partition by
Maulin.


Can the OUTPUT clause create a table? I'm doing an update like this:

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID

And I want to use the OUTPUT clause to back up my changes.

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
OUTPUT
    inserted.Id, inserted.BirthDate AS New_BirthDate,
    deleted.BirthDate AS Old_BirthDate
    INTO OutputTable
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID

What I want to know is if there is a way for the OUTPUT clause to create the table OutputTable or do I have to make sure it already exists before running the statement?

AFAIK, the target table must exist, though the documentation is not explicit and probably should be. I can't see anything in the syntax diagram that would support any type of extension to create the output table at the same time.

Use a subquery.

Into an existing table:

insert into dbo.ExistingTable
select *
from
    (
    UPDATE dbo.Table1
    SET BirthDate = b.BirthDate
    OUTPUT
        inserted.Id, inserted.BirthDate AS New_BirthDate,
        deleted.BirthDate AS Old_BirthDate
    FROM Table1 a
    JOIN Table2 b
        ON a.ID = b.ID
    ) as UpdatedRows

Creating a new table:

select *
into dbo.NewTable
from
    (
    …
    ) as UpdatedRows

The into keyword in the output clause can, as far as I know, only be used to put the results into a table-typed local variable.


Why does "GRANT SELECT ... TO Role;" not allow members of Role to SELECT? Please consider the following scenario on a Oracle 11g Database.
The user ADMIN performs the following:
CREATE USER Alice IDENTIFIED BY pwdalice;

GRANT CREATE SESSION TO Alice;

CREATE ROLE Viewer IDENTIFIED BY pwdviewer;

GRANT Viewer TO Alice;

GRANT SELECT ON Table_1 TO Viewer;

Then Alice logs into the database as 'Alice' and executes the following command:
SELECT * FROM Table_1;

Alice gets the following error:
SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I thought that granting privileges to a role would enable its member-users to get those privileges. However, this scenario shows that it is not so. What am I missing here? How can I grant SELECT to Alice using a role?
Update:
Following the answers, tried 3 fixes with no success
1) Using Fully-qualified Table Names
I missed to include the schema name in  SELECT * FROM Table_1; command. However, even after adding the schema name as shown below, still the error comes.
Alice executes:
SELECT * FROM ADMIN.Table_1;

Gets the error:
SELECT * FROM ADMIN.Table_1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

2) Using a synonym for the fully-qualified table name
Unfortunately, this does not seem to solve the problem either.
Alice executes the following:
CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1;
CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1
*
ERROR at line 1:
ORA-01031: insufficient privileges

3) Altering the session
ALTER SESSION SET current_schema = ADMIN;

Session altered.

SELECT * FROM Table_1;

SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

In addition to ensuring you're in the right schema, try this one;
Once logged in as Alice, do: SET ROLE Viewer IDENTIFIED BY pwdviewer;

For more see here

In similar situation, following worked for me.

sqlplus / as sysdba

grant connect, resource, create view, select any table, create synonym, select
any dictionary to [your user];

I know that this solution is not idle for all the cases but this was my last resort.


Switching from a SQL Server 2016 Failover Cluster to Availability Groups The DBA role fell into my lap and now I was asked to investigate Always On Availability Groups.

So, in the Availability Group environment, do all servers need to have the same specifications?
Say I have Server A with a 20 core enterprise environment, do servers B & C have to have 20 Core each?
How does Disaster Recovery work?
Currently I have a mirror to an offsite with a decent connection, do I have to up the connection bandwidth? Is the DR site part of the Availability Group?
I was under the impression (probably 100% wrong), that if the db is in recovery mode, not accessible, you don’t have to pay for the SQL Server licenses. Now with Always On sounds like you are forced to get the licenses even if you are not accessing the server.
Using Availability Groups, can I create a primary server for one group and set a different primary server for another AG, is that possible?

Do you need to have the same server configuration for both nodes?

No, this is not required, however it is a best practice. In the event that your secondary replica has fewer cores and less memory then you would see a degradation in performance when failed over to the lower powered machine. This might still give you sufficient performance, only you can make that decision, but it is not a requirement.

One thing I would recommend is to always ensure that you keep the drive letters and paths the same on all machines in an AG, otherwise you'll run into a lot of problems attempting to add data or log files.

  How does Disaster Recovery work?

You will need to license your secondary if you allow read intent routing. That is something that gets configured at the AG level. But ALWAYS check with a licensing representative to ensure that you are compliant.

You should not need additional bandwidth for AG traffic if you are already using mirroring. Packets are compressed prior to sending between machines (when running in asynchronous commit mode).

  Can I create a primary for one AG and a different AG for another?

Yes.

The limitation here is that a database can only reside within a single AG, and that all nodes must be in the same WSFC. Be aware though (going back to your licensing question) that if a server is a primary for databases then it must be appropriately licensed.

Its good to see that you are working with Alwayson.
Here are the answers for your questions:
Do I need the same config for all the nodes?
Unfortunately its YES, becuase alwayson is helping us to make the business always up. Once the Primary goes down, then Alwayson will failover to another node and make that as the new primary. So it must be have the same config to get the same performance here. Luckily this is the only case you must have same config. If you are using Availability group for Readonly purpose then make the reader with less config.
Alwayson is for DR?
NO, Human errors are also considered as DR. In this case if someone deleted a table, then this statement should immediatly replicated to Seconday. So there is no delyed replica here.
Multiple Availability Groups?
I didn't understand this question, but as per my understanding, You can have more than one availability Group, but you can't add one database to multiple availability Group.


Dog Adoption Database Design Apologies if this isn't the norm, but I am seeking advice for the database design for 'dog rescue/adoption'.

I've done my normalization research and this is my first attempt at database design. Any tips/feedbacks/criticism?

The trickiest part was dealing with dogs/users/organisations and representing who owns a dog.

a dog can belong to an organisation.
An organisation can have many organisation users.
Think animal shelter/pound
a dog can belong to an organisation user. think a one man band with a large property housing multiple dogs at a time
a dog can belong to a single user, not attached to any organisation. think someone moving overseas and they need to find their dog a new owner

So, the dog table has to have either an organisation ID or a user ID attached to it, or both. Perhaps a trigger stating that one must be not null.

You can have organisation, user, and location all have a reference to a common table representing something that can be an owner to a dog. Then dog can have a single reference to this table. Here is a diagram explaining.

dog.owner_id references can_own_dog.can_own_dog_id, in case that isn't clear. Each of the can_own_dog foreign keys is unique in their table, ideally  unique across all three tables.

I'm not sure if you're familiar with object-oriented programming, but the idea is similar to multiple classes implementing a single interface.

On another note, your diagram says "Users can have multiple locations", and "Dogs can have multiple locations". I think these mean to say "Locations can have many users" and "Locations can have many dogs"?

your table is fully normalised so that's good.

as for who lists a dog for adoption, you could use a bridging table between the organisation and user table.


Moving SQL Server database to a new disk while online I have a 1.4TB SQL Server Database that's struggling massively with disk I/O. We've installed a new SSD array into the server that will solve all of our problems, we're just debating the best way to move the database across. Ideally if we can do it without downtime, that's best. But where the choice is between two days of poor performance (e.g. while copying data) versus two hours of downtime, the latter might be preferable.

So far, the solutions we've come up with are:

Simple copy. Take the DB offline, copy the files across, change the locations in SQL Server and bring it back online. Rough figures estimate this will take up to five hours, which is not really acceptable, but it is the easiest solution.
Block-level copy. Using an rsync-like utility, we copy the files across in the background while the DB is up. When we're ready to migrate, we take the DB offline, do a differential copy across using this utility, then point SQL server at the new files and bring it online. Timing here is unknown. We don't know how long it will take to do a differential analysis of 1.4TB and copy that across. Our other concern is that the block-level copy will leave the files in some state unreadable by SQL Server and we'll have waste our time.
SQL migration. Create a new 1.4TB SQL data file on the new disk and disable autogrowth on all of the other files. Then run DBBC SHRINKFILE(-file_name-, EMPTYFILE) on all of the other data files in turn. Once all of the data is across, I'll take a scheduled window at some point to move the MDF file over to the SSD and remove the other unused files. I like this because it minimises downtime. But I have no idea how long this will take and whether it will cause degradation in performance while it's happening.

We don't have any kind of load & performance environment to test this. I can verify that the strategies will work on our staging environment, but not the impact and not the performance.

One method to move the entire database is with BACKUP and RESTORE.  The database will be unavailable during the final switch but downtime should be minimal with planning.  This procedure assumes the FULL or BULK_LOGGED recovery model:

1) Perform a FULL backup (or use your existing one).

2) Restore latest full backup to a different database name, specifying the WITH MOVE option to relocate files on SSD storage as desired and the NORECOVERY option so that subsequent differential and log backups can be restored.

3) Apply incremental changes to the new database until the time of the final cut-over with transaction log backups and RESTORE...WITH NORECOVERY.  This will minimize downtime for the final switch to the new database.

4) To switch to the new database, take the application offline, perform a final transaction log backup, and apply to the new database WITH RECOVERY.  Finally, rename the original database to a different name and rename the relocated database to the original name.  Drop the old database at your convenience.

In the SIMPLE recovery model, you could use a similar process but without the step 3 transaction log backup/restores.  Instead, use a differential database backup/restore in the final step.  That may require more downtime, depending on the amount of changes since the initial FULL backup.

Good approach is to use SQL REPLICATION between two server
once all data replicated on SSD server then take current server offline
and switch to SSD server.


TABLESPACE creation error I have an error "ORA-01537" when I create a tablespace.

create tablespace "VOLUMT" datafile 'VOLUMT.dbf' size 250M autoextend on;

Then, it shows like following.

  ERROR at line 1: ORA-01537: cannot add file 'VOLUMT.dbf' - file
  already part of database

I just change the tablespace name of VOLUMT to TEST01, and it works..

  Tablespace created.

So, I get into the directory of db and searched the related tablespace..

${HOME}/oracle/db/oradata/ORAC12

However, I can't find any.. Coudl you help me to solve this problem? Also I need to use "VOLUMT" for tablespace creation.. not TEST01..

-- Additional --

select TABLESPACE_NAME from DBA_DATA_FILES;

TABLESPACE
----------
SYSTEM
SYSAUX
USERS
TEST01

I can see TEST01 Tablespace, but I can't find any datafiles in the directory.
Also I can't find any VOLLMT tablespace in it..

Thank you.

all the guys..

First of all, really appreciate to you all for replying...

When I input the follwing

select   file_name,
         file_id,
         tablespace_name,
         status,online_status
from     dba_data_files
order by tablespace_name;

The output is

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE STATUS    ONLINE_
---------- ---------- --------- -------
/home/oracle/db/oradata/ORAC12/ID/sysaux01.dbf
    28 SYSAUX     AVAILABLE ONLINE

/home/oracle/db/oradata/ORAC12/ID/system01.dbf
    27 SYSTEM     AVAILABLE SYSTEM

/home/oracle/db/oradata/ORAC12/ID/ID_user01.dbf
    29 USERS      AVAILABLE ONLINE

There is no such as "VOLUMT" tablespace. When I re-try to create a "TEST01" tablespace, eventhough it's already deleted, it shows 'the file exists already'..

When I get into the directory, there are other SID's as well. And, one of them has "VOLUMT". However, they are different "SID" so they can have same name of tablespace, isn't it?

delete the offending tablespace and it's contents. use

DROP TABLESPACE 'the tablespace name' INCLUDING CONTENTS;

for your case;

DROP TABLESPACE VOLUMT INCLUDING CONTENTS;


SQL Server: Create New GUID Values for Existing Index I have an existing table with about 6 million rows. The relevant columns are:

ID int not null PK
Key uniqueidentifier not null

Read/write on this table by Key lookup is probably something like 100 to 1.

The existing rows were all created using newid() so they are not in sequence. An index exists for Key.

CREATE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY]

Given that there are already millions of rows in the table, is there any benefit to changing the GUID generation to sequential using newsequentialid()?

Going forward it would generate better data, but since it's unknown where within the index the new sequential GUID series would land, would it just screw up the index faster? Would it be better to leave the GUID generation non-sequential so that the remaining page space in the index fills more evenly?

As far as the growth rate of the table, it represents about 3 years of data.

Year    NumRows
2012    3962660
2013    1661189
2014    711241

Where 2014 is halfway through the year as of this question. There were some code changes at some point that reduced the rate of inserts, so I believe 2013 would be a typical annual row count.

You may be able to reduce index rebuild/reorg times by moving to NEWSEQUENTIALID().

You are currently inserting around 4,500 rows into the table per day.  The table has around 6.3 million rows at present.  That's around 0.7% of the table per day.  Assuming every insert on the table results in an index-page split, this will result in 9,000 pages per day written to the storage system.  This will create some unnecessary I/O pressure both for inserts into the index, and for rebuild/reorgs.  Switching the Key field to sequential ID generation will vastly reduce this I/O pattern.

If you switch to NEWSEQUENTIALID(), the code that performs INSERTs on the table will need to be sensitive the possibility that it will attempt to insert a duplicate Key.  This is a remote possibility, however it is still a possibility, and is the same for both NEWID() and NEWSEQUENTIALID() - since nothing is truly random that is generated by a computer.  Queries against the index could gain some benefit by making the index unique, as in:

CREATE UNIQUE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY];

This change would make index scans less likely.  Paul White has an excellent article about this at https://sqlkiwi.blogspot.com/2011/02/seeking-without-indexes.html

Since you haven't given any indication of the type of queries you run against this table, I will assume you are at some points scanning the entire index nothing about your situation.  If your data is truly stored on a single spinning disk, then having your index non-fragmented should reduce the time needed for queries to return information.

Having said that, you could likely get a whole lot more for your money (time is money) by improving the IO subsystem so index rebuild/reorgs are unnecessary.  See http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ .

If you are interested in measuring the effects of the index settings you are using for the Key column, you could monitor page free percentage (among other things) for pages used by the index by using the system dmv, sys.dm_exec_index_physical_stats.  For example:

SELECT o.name, ps.avg_page_space_used_in_percent, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('KeyTable','Table'), NULL, NULL, 'DETAILED') ps
    INNER JOIN sys.objects o ON ps.object_id = o.object_id;

(You may want to adjust the parameters passed to only show the index on the Key column.)

Once the avg_page_space_used_in_percent crosses some boundary value such as 95% (or more), you would then rebuild the index, which both defragments it, and with an appropriate FILL_FACTOR setting, leaves some space free on each page for new rows with randomly generated NEWID() values.

If you did decide to use NEWSEQUENTIALID() your index could use a FILL_FACTOR of 100% since index pages would not longer be split on a frequent basis.  This has the benefit of reducing the number of pages required to hold the index by 20% (against your current settings), thereby reducing the IO required to rebuild the index when that does become necessary.

Since you are only inserting new rows at a rate of about 0.07% of the total table per day, it may take a very long time before the system automatically triggers a histogram update on the statistics for the table involved.  You could use the STATS_DATE() function to ensure the statistics are never more than x days old.  Up-to-date statistics are vital for the query engine to produce optimal query plans.

"…is there any benefit to changing the GUID generation to sequential using newsequentialid()?"

No.

Sequential GUIDs are only appropriate when there is a clustered index on the GUID column and you want to avoid page splits caused by inserts.

Edit to address the comment below: All nonclustered indexes suffer from page splits when data is inserted. For example, when you enter a record for Homer Simpson, it gets entered into the "S" leaf pages for the LastName index, possibly causing a page split. You don't however, require that customers join in strict alphabetical order.

Additionally, the latch system used for index leaf and non-leaf pages means that page splits require less processing time and resource than page splits on data pages.

Further to this, what would it require for the OP to change to a sequential id? They would have to replace the Default constraint on the Key column to NEWSEQUENTIALID(). This will not affect any existing rows in the table (which is good because there are foreign keys using those keys) - just the new rows. From that point on, inserted rows will have increasing keys but those increasing keys are not necessarily going to be greater than the existing data in the table (NEWSEQUENTIALID() only guarantees that the GUID is greater than any other GUID generated by NEWSEQUENTIALID() on that computer since it restarted). This means whose inserts are still going to cause page splits in the nonclustered index!


Change query to improve operator estimates I have a query which runs in an acceptable amount of time but I want to squeeze the most performance possible from it.

The operation I'm trying to improve is the "Index Seek" at the right of the plan, from Node 17.

I've added appropriate indexes but the estimates that I get for that operation are half of what they are supposed to be.

I've looked for changing my indexes and adding a temporary table and re-writing the query, but I couldn't simplify it more than this in order to get the right estimates.

Does anyone have any suggestions on what else I can try?

The full plan and its details can be found here.

The non-anonymized plan can be found here.

Update:

I have a feeling the initial version of the question raised a lot of confusion, so I'm going to add the original code with some explanations.

create procedure [dbo].[someProcedure] @asType int, @customAttrValIds idlist readonly
as
begin
    set nocount on;

    declare @dist_ca_id int;

    select *
    into #temp
    from @customAttrValIds
        where id is not null;

    select @dist_ca_id = count(distinct CustomAttrID)
    from CustomAttributeValues c
        inner join #temp a on c.Id = a.id;

    select a.Id
        , a.AssortmentId
    from Assortments a
        inner join AssortmentCustomAttributeValues acav
            on a.Id = acav.Assortment_Id
        inner join CustomAttributeValues cav
            on cav.Id = acav.CustomAttributeValue_Id
    where a.AssortmentType = @asType
        and acav.CustomAttributeValue_Id in (select id from #temp)
    group by a.AssortmentId
        , a.Id
    having count(distinct cav.CustomAttrID) = @dist_ca_id
    option(recompile);

end

Answers:

Why the odd initial naming in the pasteThePlan link?

Answer: Because I used anonymize plan from SQL Sentry Plan Explorer.
Why OPTION RECOMPILE?

Answer: Because I can afford recompiles in order to avoid parameter sniffing (the data is / could be skewed). I have tested and I am happy with the plan that the Optimizer generates while using OPTION RECOMPILE.
WITH SCHEMABINDING?

Answer: I'd really want to avoid that and would use it only when I have a indexed view. Anyway, this is a system function (COUNT() ) so no use for SCHEMABINDING here.

Answers to more possible questions:

Why do I use INSERT INTO #temp FROM @customAttrributeValues?

Answer: Because I noticed and now know that when using variables plugged into a query, any estimates that come out of working with a variable is always 1. And I tested putting the data into a temp table and the Estimated is then equal with Actual Rows.
Why did I use and acav.CustomAttributeValue_Id in (select id from #temp)?

Answer: I could have replaced it with a JOIN on #temp, but developers were very confused and proffered the IN option. I don't really think there would be a difference even by replacing and either way, there is no problem with this.

The plan was compiled on a SQL Server 2008 R2 RTM instance (build 10.50.1600). You should install Service Pack 3 (build 10.50.6000), followed by the latest patches to bring it up to the (current) lastest build 10.50.6542. This is important for a number of reasons, including security, bug fixes, and new features.

The Parameter Embedding Optimization

Relevant to the present question, SQL Server 2008 R2 RTM did not support the Parameter Embedding Optimization (PEO) for OPTION (RECOMPILE). Right now, you are paying the cost of recompiles without realizing one of the main benefits.

When PEO is available, SQL Server can use the literal values stored in local variables and parameters directly in the query plan. This can lead to dramatic simplifications and performance increases. There is more information about that in my article, Parameter Sniffing, Embedding, and the RECOMPILE Options.

Hash, Sort and Exchange Spills

These are only displayed in execution plans when the query was compiled on SQL Server 2012 or later. In earlier versions, we had to monitor for spills while the query was executing using Profiler or Extended Events. Spills always result in physical I/O to (and from) the persistent storage backing tempdb, which can have important performance consequences, especially if the spill is large, or the I/O path is under pressure.

In your execution plan, there are two Hash Match (Aggregate) operators. The memory reserved for the hash table is based on the estimate for output rows (in other words, it is proportional to the number of groups found at runtime). The memory granted is fixed just before execution begins, and cannot grow during execution, regardless of how much free memory the instance has. In the supplied plan, both Hash Match (Aggregate) operators produce more rows than the optimizer expected, and so may be experiencing a spill to tempdb at runtime.

There is also a Hash Match (Inner Join) operator in the plan. The memory reserved for the hash table is based on the estimate for probe side input rows. The probe input estimates 847,399 rows, but 1,223,636 are encountered at run time. This excess may also be causing a hash spill.

Redundant Aggregate

The Hash Match (Aggregate) at node 8 performs a grouping operation on (Assortment_Id, CustomAttrID), but the input rows are equal to the output rows:

This suggests the column combination is a key (so the grouping is semantically unnecessary). The cost of performing the redundant aggregate is increased by the need to pass the 1.4 million rows twice across hash partitioning exchanges (the Parallelism operators on either side).

Given that the columns involved come from different tables, it is more difficult than usual to communicate this uniqueness information to the optimizer, so it can avoid the redundant grouping operation and unnecessary exchanges.

Inefficient thread distribution

As noted in Joe Obbish's answer, the exchange at node 14 uses hash partitioning to distribute rows among threads. Unfortunately, the small number of rows and available schedulers means all three rows end up on a single thread. The apparently-parallel plan runs serially (with parallel overhead) as far as the exchange at node 9.

You can address this (to obtain round-robin or broadcast partitioning) by eliminating the Distinct Sort at node 13. The easiest way to do that is to create a clustered primary key on the #temp table, and perform the distinct operation when loading the table:

CREATE TABLE #Temp
(
    id integer NOT NULL PRIMARY KEY CLUSTERED
);

INSERT #Temp
(
    id
)
SELECT DISTINCT
    CAV.id
FROM @customAttrValIds AS CAV
WHERE
    CAV.id IS NOT NULL;

Temporary table statistics caching

Despite the use of OPTION (RECOMPILE), SQL Server can still cache the temporary table object and its associated statistics between procedure calls. This is generally a welcome performance optimization, but if the temporary table is populated with a similar amount of data on adjacent procedure calls, the recompiled plan may be based on incorrect statistics (cached from a previous execution). This is detailed in my articles, Temporary Tables in Stored Procedures and Temporary Table Caching Explained.

To avoid this, use OPTION (RECOMPILE) together with an explicit UPDATE STATISTICS #TempTable after the temporary table is populated, and before it is referenced in a query.

Query rewrite

This part assumes the changes to the creation of the #Temp table have been made already.

Given the costs of possible hash spills and the redundant aggregate (and surrounding exchanges), it may pay to materialize the set at node 10:

CREATE TABLE #Temp2
(
    CustomAttrID integer NOT NULL,
    Assortment_Id integer NOT NULL,
);

INSERT #Temp2
(
    Assortment_Id,
    CustomAttrID
)
SELECT
    ACAV.Assortment_Id,
    CAV.CustomAttrID
FROM #temp AS T
JOIN dbo.CustomAttributeValues AS CAV
    ON CAV.Id = T.id
JOIN dbo.AssortmentCustomAttributeValues AS ACAV
    ON T.id = ACAV.CustomAttributeValue_Id;

ALTER TABLE #Temp2
ADD CONSTRAINT PK_#Temp2_Assortment_Id_CustomAttrID
PRIMARY KEY CLUSTERED (Assortment_Id, CustomAttrID);

The PRIMARY KEY is added in a separate step to ensure the index build has accurate cardinality information, and to avoid the temporary table statistics caching issue.

This materialization is quite likely to occur in memory (avoiding tempdb I/O) if the instance has enough memory available. This is even more likely once you upgrade to SQL Server 2012 (SP1 CU10/SP2 CU1 or later), which has improved Eager Write behaviour.

This action gives the optimizer accurate cardinality information on the intermediate set, allows it to create statistics, and allows us to declare (Assortment_Id, CustomAttrID) as a key.

The plan for the population of #Temp2 should look like this (note the clustered index scan of #Temp, no Distinct Sort, and the exchange now uses round-robin row partitioning):

With that set available, the final query becomes:

SELECT
    A.Id,
    A.AssortmentId
FROM
(
    SELECT
        T.Assortment_Id
    FROM #Temp2 AS T
    GROUP BY
        T.Assortment_Id
    HAVING
        COUNT_BIG(DISTINCT T.CustomAttrID) = @dist_ca_id
) AS DT
JOIN dbo.Assortments AS A
    ON A.Id = DT.Assortment_Id
WHERE
    A.AssortmentType = @asType
OPTION (RECOMPILE);

We could manually rewrite the COUNT_BIG(DISTINCT... as a simple COUNT_BIG(*), but with the new key information, the optimizer does that for us:

The final plan may use a loop/hash/merge join depending on statistical information about the data that I do not have access to. One other small note: I assumed that an index like CREATE [UNIQUE?] NONCLUSTERED INDEX IX_ ON dbo.Assortments (AssortmentType, Id, AssortmentId); exists.

Anyway, the important thing about the final plans is that the estimates should be much better, and the complex sequence of grouping operations has been reduced to a single Stream Aggregate (which does not require memory and therefore cannot spill to disk).

It is hard to say that performance will actually be better in this case with the extra temporary table, but the estimates and plan choices will be much more resilient to changes in data volume and distribution over time. That may be more valuable over the long term than a small performance increase today. In any case, you now have much more information on which to base your final decision.

You are not going to improve from a [non-clustered] Index Seek. The only thing better than a non-clustered index seek is a Clustered Index Seek.

Also, I've been a SQL DBA for the last ten years, and a SQL developer for five years before that, and in my experience it is extremely rare to find an improvement to a SQL Query by studying the execution plan that you couldn't find by other means. The main reason to generate the execution plan is because it will often suggest missing indexes to you that you can add to improve performance.

The main performance gains will be in adjusting the SQL Query itself, if there are any inefficiencies there. For example, a couple months ago I got a SQL function to run 160 times faster by rewriting a SELECT UNION SELECT style pivot table to use the standard SQL PIVOT operator.

insert into Variable1 values (?), (?), (?)

select *
    into Object1
    from Variable2
        where Column1 is not null;

select Variable3 = Function1(distinct Column2)
    from Object2 Object3
        inner join Object1 Object4 on Object3.Column1 = Object4.Column1;

select Object4.Column1
        , Object4.Column3
    from Object5 Object4
        inner join Object6 Object7
            on Object4.Column1 = Object7.Column4
        inner join Object2 Object8
            on Object8.Column1 = Object7.Column5
    where Object4.Column6 = Variable4
        and Object7.Column5 in (select Column1 from Object1)
    group by Object4.Column3
        , Object4.Column1
    having Function1(distinct Object8.Column2) = Variable3
    option(recompile);

So let's see, SELECT * INTO is generally less efficient than a standard INSERT Object1 (column list) SELECT column list. So I would rewrite that. Next, if Function1 was defined without a WITH SCHEMABINDING, adding a WITH SCHEMABINDING clause should allow it to run faster.

You've chosen a lot of aliases that don't make sense, like aliasing Object2 as Object3. You should choose better aliases that don't obfuscate the code. You have "Object7.Column5 in (select Column1 from Object1)".

IN clauses of this nature are always more efficient written as EXISTS (SELECT 1 FROM Object1 o1 WHERE o1.Column1 = Object7.Column5). Perhaps I should have written that the other way. EXISTS will always be at least as good as IN. It's not always better, but usually is.

Also, I doubt that option(recompile) is improving the query performance here. I would test removing it.


PHP-MYSQL messaging system performance problems I want to create a messaging module for the social network web-site project (PHP & MySQL) I've been working on. What substructures do well-known sites use for modules like this?

I think, generating a new record on MySQL for each of new messages will slow down the system and cause problems after a while. What would you suggest me for saving the datas? SQLLite, file system or a high-performance dbms ? (exp: postgresql)

Thanks

You can use a database such as mySQL just fine. Especially if the table structure is simple and indexed well.  I would presume a record for each message would create a need for inserts, but not updates, which would keep index rebuilding needs low too.

If you wanted to scale, just create a read-replica for the querying of the messages and send the write queries to the master server.

Want to scale even more, I suppose you could implement some sort of memcached intermediate level, but this would help more on expanding performance on the read side, not the write side particularly (since you'd have to write them eventually, and you could lose them in memcached in the interim potentially).

In short - mySQL is fine.  Other options include cloud options from amazon like DynamoDB and/or nosql options, but not essential.

You can start with mysql. Keep all your table structures simple, do not use auto increments, and keep to basic data types like varchars, ints, etc. And index the table correctly.

Use pdo to access the database, and also write your own classes to completely abstract out the data access.

This way, when your site gets popular and the performance start to lag, you can easily switch to a higher performance dbms, or better hardware infra to increase performance.


Can I reorganise index on a sql server 2005 database without performance hit on production server I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. Please correct me if I am wrong. But I am wondering if I can do this without any performance hit or not.

Correct: online index rebuilds are only supported in the Enterprise edition engine.

ALTER INDEX ... REORGANIZE is always an online operation, regardless of your edition of SQL Server, even on 2005.

Doing this while the system is in operation will certainly affect performance as far as physical disk workload, but it should not affect read or modification operations in terms of locking. See the link above for more details.

Since doing a REORGANIZE is online, you can choose to schedule it more frequently, perhaps even taking advantage of periods of slower activity. The key idea would be to do it more frequently, but do less work each time, as compared to, say, doing it once/day or once/week.

Maybe this misses your point, but could you recreate the index with a new name, drop the old index, and rename the new kne into its place? You'd still have the performance hits from the reads to create the index, but statements running in the meantime can continue to use the old index until the new one I ready.


How to query last restore date in SQL Server? Is there a T-SQL query that shows the last restore datetime for a certain database?

This will list all "most recent" restores for each database on your server:

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

Select Destination_database_name,
       restore_date,database_name as Source_database,
       Physical_device_name as Backup_file_used_to_restore
from msdb.dbo.restorehistory rh
  inner join msdb.dbo.backupset bs
    on rh.backup_set_id=bs.backup_set_id
  inner join msdb.dbo.backupmediafamily bmf
    on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC

For detailed information you can check below link:

http://www.passionforsql.com/how-to-find-when-last-database-backuprestore-was-done/


mysql_install_db: How to set the root password mysql 5.6 seems to have a new --random-passwords option for mysql_install_db, which lets me discover the root password:
http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html#option_mysql_install_db_random-passwords

But what can I do with mysql 5.5? The documentation suggests that the root password should be empty, but I cannot seem to connect with no password (or an empty password), so i cannot set a real root password:

For instance:

I initialize the database like so:

$ mysql_install_db --no-defaults --user=murrayc --datadir=/tmp/testmysql/data

I start the server like so:

$ mysqld_safe --no-defaults --user=murrayc --port=3308 --datadir='/tmp/testmysql/data' --socket='/tmp/testmysql/mysqld.sock' --pid-file='/tmp/testmysql/pid'

But when I try to set the root password like so (I believe that --user in this case refers to the mysql user, not the linux user):

$ mysqladmin --no-defaults --port=3308 --user=root --password='' password 'somenewpassword'

I get this error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Upon installation, root@localhost does not have a password.

You should be able to connect without a password by just doing this:

$ mysql -uroot

Try the following:

$ mysqladmin --no-defaults --port=3308 --user=root password 'somenewpassword'

or you can do it the stubborn way:

$ service mysql restart --skip-grant-tables --skip-networking
$ mysql -e"UPDATE mysql.user SET password=password('somenewpassword') WHERE user='root'"
$ service mysql restart
$ mysql -uroot -p

UPDATE 2013-01-02 16:47 EDT

I am sorry, I overlooked the port number.

Here is the problem: you cannot use root@localhost against a non-3306 port unless you use the TCP/IP protocol. Please try this:

$ mysqladmin --no-defaults --port=3308 --user=root --protocol=tcp password 'somenewpassword'

Try to run mkdir -p var tmp before mysql_install_db.

create mysql db failed in my situation.

If var and tmp are Not created previously and use them as the paras of mysql_install_db,
we can start mysqld successfully.
try to use mysql or mysqladmin connect to server, I got the err:
access denied for 'root'@'localhost'

Maybe the question's cause is the same with mine.


SQL Server Linked Servers and Remote Statistics I'm trying to get a better understanding of how SQL Server uses statistics on remote SQL Servers (e.g. a Linked Server). I understand that the user needs db_owner/db_ddladmin rights to be able to use the stats on the remote box. At first this seems really straight forward based on the below text from Microsoft.

  To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

The pieces that is confusing me is the word "User". We use a standard security context for all connections rather than the user's login. This login/user has the needed permissions as defined above over the databases in question. Additionally, we do not use Impersonation.

I have tried running queries and capturing the traces on both ends using accounts with and without elevated permissions on the remove server. It looks to me like the remote server is not collecting statistics unless the actual user (the guy at the keyboard) has the elevated rights. I believe this to be true because I am only seeing calls to sys.sp_table_statistics2_rowset when the user has the needed rights.

I really hope this makes sense.

Does anyone have an understanding of how this really works in SQL 2008 R2 and below?

What I do is this 'trick':

Give this 'linkuser' read, whatever else needed and ddl_admin privileges, then run the following script to ensure that none of the ddl_admin privileges can actually be used.

But by being technically member of ddl_admin the user can now see statistics in the remote db! (note: from SQL2012SP1 there is no need for this hack, the stats are visible as they should be)

DENY CREATE TABLE TO [LinkUser]
DENY CREATE VIEW TO [LinkUser]
DENY CREATE PROCEDURE TO [LinkUser]
DENY CREATE FUNCTION TO [LinkUser]
DENY CREATE RULE TO [LinkUser]
DENY CREATE DEFAULT TO [LinkUser]
DENY CREATE TYPE TO [LinkUser]
DENY CREATE ASSEMBLY TO [LinkUser]
DENY CREATE XML SCHEMA COLLECTION TO [LinkUser]
DENY CREATE SCHEMA TO [LinkUser]
DENY CREATE SYNONYM TO [LinkUser]
DENY CREATE AGGREGATE TO [LinkUser]
DENY CREATE MESSAGE TYPE TO [LinkUser]
DENY CREATE SERVICE TO [LinkUser]
DENY CREATE CONTRACT TO [LinkUser]
DENY CREATE REMOTE SERVICE BINDING TO [LinkUser]
DENY CREATE ROUTE TO [LinkUser]
DENY CREATE QUEUE TO [LinkUser]
DENY CREATE SYMMETRIC KEY TO [LinkUser]
DENY CREATE ASYMMETRIC KEY TO [LinkUser]
DENY CREATE FULLTEXT CATALOG TO [LinkUser]
DENY CREATE CERTIFICATE TO [LinkUser]
DENY CREATE DATABASE DDL EVENT NOTIFICATION TO [LinkUser]
DENY CHECKPOINT TO [LinkUser]
DENY ALTER ANY SCHEMA TO [LinkUser]
DENY ALTER ANY ASSEMBLY TO [LinkUser]
DENY ALTER ANY DATASPACE TO [LinkUser]
DENY ALTER ANY MESSAGE TYPE TO [LinkUser]
DENY ALTER ANY CONTRACT TO [LinkUser]
DENY ALTER ANY SERVICE TO [LinkUser]
DENY ALTER ANY REMOTE SERVICE BINDING TO [LinkUser]
DENY ALTER ANY ROUTE TO [LinkUser]
DENY ALTER ANY FULLTEXT CATALOG TO [LinkUser]
DENY ALTER ANY SYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY ASYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY CERTIFICATE TO [LinkUser]
DENY REFERENCES TO [LinkUser]
DENY ALTER ANY DATABASE DDL TRIGGER TO [LinkUser]
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO [LinkUser]

Well,
it really depends how you set up the security context of the linked server, right? If the guy at sitting at the keyboard doesn't have any rights on the remote server, then how is the connection made: will it cut it off, will it use the current security context, or will it impersonate a particular login on the remote server? I think this is where you need to focus your attention.
In our PROD environment, we have set up a user that has read, write and ddl admin privileges and all the connections are made using the security context of that remote login/password.
I hope this helps.


How to design a relationship database table to store friendship relationship? I want to design a table to store friendship relationship in my web project
It should satisfy at least the following 4 conditions:

who send the add-friend request   e.g.(if A TO B then this column will be A)
who receive the add-friend request e.g.(if A TO B then this column will be B)
current status e.g.(0 denotes rejected whereas 1 denotes accepted or 2 denotes unprocessed
our friend relationship is bilateral

If any of you are experienced with this , any suggestion is welcomed
my current design (I think bad right now) is like this these are the columns
frienshipId
fromUserId
toUserId
status
requestTime

I'd create a table a lot like the one that you have.  I'm using SQL Server data types and syntax, you may need to tweak depending on your platform.

CREATE TABLE FriendStatus
(FriendStatusId BIGINT PRIMARY KEY IDENTITY(1,1),
FromUserId BIGINT,
ToUserId BIGINT,
StatusId TINYINT,
SentTime DATETIME2,
ResponseTime DATETIME2);

Indexing of the table will be critical as the table grows to the tens and hundreds of millions.

Schema:

CREATE TABLE users (
    users_id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);

CREATE TABLE friends (
    friends_id serial PRIMARY KEY,
    timestamp TIMESTAMPTZ default now(),
    user_a integer NOT NULL REFERENCES users,
    user_b integer NOT NULL REFERENCES users,
    status integer NOT NULL default 2
)

From PHP:

select *
from friends
where user_a=$myid or user_b=$myid


Guid vs INT - Which is better as a primary key? I've being reading around reasons to use or not Guid and int.

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why?

From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Why exactly was Guid created? I actually think it has a purpose other than serving as primary key of a simple table. (Any example of a real application using Guid for something?)

( Guid = UniqueIdentifier ) type on SQL Server

This has been asked in Stack Overflow here and here.
Jeff's post explains a lot about pros and cons of using GUID.

###GUID Pros

Unique across every table, every database and every server
Allows easy merging of records from different databases
Allows easy distribution of databases across  multiple servers
You can generate IDs anywhere, instead of having to  roundtrip to the database, unless partial sequentiality is needed (i.e. with newsequentialid())
Most replication scenarios require GUID  columns anyway

###GUID Cons

It is a whopping 4 times larger than the traditional 4-byte index
value; this can have serious
performance and storage implications
if you're not careful
Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
The generated GUIDs should be partially sequential for best
performance (eg, newsequentialid() on
SQL Server 2005+) and to enable use of
clustered indexes

If you are certain about performance and you are not planning to replicate or merge records, then use int, and set it auto increment (identity seed in SQL Server).

The answer by rmirabelle is what I do. However, for larger-scale projects, there is an ultimate design:
Use: a Key Mapping Table
TableA

- ID int (PK)
- Data varchar(100)

TableAMap

- ID int (PK)
- UniversalID GUID (Indexed - nonclustered)

GUIDs are seldom needed for database replication/import/export. So, instead of having the GUID on the main table, where it takes up an extra 8-bytes per row, and where a GUID index will be (by default) stored on the same volume; a separate table (aka normalization) comes to the rescue.
With a separate table, your DBAs are free to store it on another slower disk. Also, if the GUID is ONLY needed for certain batch jobs, you can create the GUID index just before it's needed, then drop it after.


PL/SQL: best way to count elements in an array? Given this:

DECLARE
  TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY;
  V_COUNT INTEGER;

I would like to do:

BEGIN
  -- ... some code filling the MY_ARRAY array

  -- obviously COUNT_ELEMENTS() does not exists, this is what I'm looking for :-)
  V_COUNT := COUNT_ELEMENTS(MY_ARRAY);

  DBMS_OUTPUT.PUT_LINE('My array containts ' || V_COUNT || ' elements.');
END;

Is there something better than creating a procedure doing a basic loop incrementing a counter? Maybe a PL/SQL native function already does this COUNT_ELEMENTS()?

I think this is what you're after:

V_COUNT := MY_ARRAY.COUNT;

A basic method is:

for i in my_array.first .. my_array.last
loop
   v_count:=v_count+1;
end loop;


Add users to multiple databases I have 980 DBs on 1 server and I am supposed to grant readonly access to a couple of users.

I certainly don't want to run alter DB add user... statement since it is definitely time consuming.

I don't know if exec sp_addrolemember 'db_datareader', 'user' works?

Can someone let me know if something else can be done?

Here's how I would do it in Powershell:

$instance = 'localhost'
$dbs = dir SQLSERVER:\SQL\$instance\DEFAULT\Databases
$sql = 'CREATE USER [foo] FROM LOGIN [foo]; ALTER ROLE db_datareader ADD MEMBER [foo];'
$dbs | ForEach-Object {Invoke-Sqlcmd -ServerInstance $instance -Database $_.Name -Query $sql}

This assumes the login foo has already been created. It also assumes running on the localhost instance.

using an undocumented procedure you could use the SP_msforeachdb procedure.

For example, the execution would be something along the lines of

exec sp_msforeachdb '
use ?
if (dbid(''?'') > 4 )
BEGIN
    create user [user] from login [login];

    alter role db_datareader add member [user];'
END

This isn't the most elegant solution but if your simply looking to add a login to every user database this would work.

A quick note, if your using sql server 2008 R2 or older you would want to replace the alter role with exec sp_addrolemember as the alter role syntax was only added as of SQL Server 2012 and above.


mysqldump collation issues I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment.
The problem I am facing now is that our wp database after exporting, using the script, and then importing, using phpmyadmin, loses some special characters. Most noteably the € sign.
Phpmyadmin lists the database as latin1_swedish_ci and that is the collation setting I am selecting in the Import tab of phpmyadmin (iso-8859-1).
I am using the following script to export the database:

  mysqldump --user=user --password=password --default-character-set=latin1 --skip-set-charset databasename -r wp-db.sql

    tar -cpzf /home/backups/wp-backups/website-wp-$(date +"%d-%m-%Y--%H-%M").tar.gz wp-db.sql && rm wp-db.sql

My best guess is that there must be some issue with collation but I am not aware where some type of conversion happens in the steps that I am taking. At some point the € must be dropped.
When inspecting the wp-db.sql using file it is described as iso-8859-1, hence I wonder where conversion takes place.
I have since tried to test some things and discovered that I am not capable of converting a UTF-8file to iso-8859-1if it includes a € sign. At least not using

  iconv -f UTF-8 -t ISO_8859-1 test

From what I gather mysql runs in UTF-8 natively, might that be the issue?

If I do not bother with any of this it will completely ruin things. Pretty much everything that isnt wp files but stored in the database will be displayed incorrectly or will be completely missing. By adding these two arguments to my script I have eliminated most of the issues apart from special character problems.

"displayed incorrectly" -- That does not necessarily mean that the text is stored incorrectly.  How many steps do you have here?

You have data in a WP database?  Please provide SHOW CREATE TABLE for one of the tables so we can verify whether it is latin1 or utf8.  Also, do SELECT col, HEX(col) to see what you have in the table for €.  For utf8, the hex should be E282AC.  As you pointed out, it cannot be stored in latin1.  If you don't see that hex, stop; we need to debug things before this point.  Else continue...

Then you are doing a dump?  Let mysqldump specify the CHARACTER SET (not COLLATION).

Take a peek at the dump file.  Well, this may be useless, because it depends on what tool you use to peek at it.

Then you are reloading the data?  After reloading, what does the hex say?  Again, stop if it is not that same hex.

Now you are running WP and trying to display stuff.  On a web page?  Look at the HTML; there should be a <meta... > tag very near the start.  What does it say?  In particular, does it say charset=UTF-8?

One more thing to check is the SET NAMES utf8 (or equivalent) -- phpmyadmin establishes the CHARACTER SET one place; find it.  And find it for WP.


Bigfile in oracle 12 is full with 120G Hello I have The tablespace with 8192 and According with this Link From Oracle BigFile
I can have 32,768 GB In my tablespace but one of my table have over 1 billion records that make datafile over the 120GB and I getting error with ORA-01652: unable to extend temp segment by XXX in tablespace so what happening here why with 120GB I'm reaching my limit in datafile?
CREATE BIGFILE TABLESPACE TABLE_SPACE
    DATAFILE
        '~\TABLE_SPACE.DBF' SIZE 127301910528 AUTOEXTEND ON NEXT 8192 MAXSIZE 35184372064256
    BLOCKSIZE 8192
    DEFAULT NOCOMPRESS NO INMEMORY
    ONLINE
    SEGMENT SPACE MANAGEMENT AUTO
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ORA-01652: unable to extend temp segment by XXX

This isn't about your 120GB bigfile data tablespace at all.
Your TEMP tablespace isn't big enough to handle whatever sorts or temporary result sets are created by the queries that are being run in your database. If it can't autoextend, then it is probably because you have filled up the disk drive and you don't have room for more, or because the TEMP tablespace data file(s) have reached the maximum size allowed by the file system (typically 32GB each).
If you still have space available on the file system holding your TEMP tablespace, then add one or more more files to the TEMP tablespace. If the partition is completely full, then work with your system and/or storage administrator to determine how to enlarge the file system or where else you could place additional data files.

You need more free blocks in your temp tablespace, you should manually extend your temp file(s) size in order to complete your operation.


What is the pgAgent service account? Im installing pgAgent using Stack Builder.

In one step ask me for service account. Should I use PC, Domain or DB user? Or create a new one?

I try using my DOMAIN\my.user.name  also using the postgres user and return same error.

I dont have the PC_Name\Administrator password

  User creation failed. The password specified is incorrect or length of the user name is longer than supported

You should specify

System username

System password

e.g. username and password one of the windows account


How do I completely reset ServiceBroker including all messages, queues, triggers, etc? I have a database where I have a ServiceBroker up and running. For unknown reasons we have experienced a lot of problems with missing conversation handlers;

I.e.

The conversation handle "D33EC01C-CEA9-E811-95C5-00155D009F03" is not found.

It is my belief that this has happened when DB has been restarted while conversations have been "open" if that makes any sense.

What I wish to do now is to "reset" the ServiceBroker, remove all conversations, messages, triggers(!) etc.

Unfortunately I haven´t seen any documentation on how to achieve this.

Ideas?

Its cause due to multi-trigger on the table which created by service broker.

Delete all trigger and start broker services or then try.


Intermittent RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Stats I'm trying to troubleshoot some intermittent CPU spikes that we're witnessing on one of our production SQL Servers.  We're running SQL Server 2008 R2 Standard Edition with 28 GB RAM and 4 CPU cores.  When this happens, we are noticing a large number of RESOURCE_SEMAPHORE_QUERY_COMPILER waits, which lasts for about a minute or two and then stops, which then the CPU usage returns to normal.

After researching this, I understand that this is normally caused by compiling lots of non-reusable execution plans, which we are currently working on changes to our application to address.

Can this behavior also be triggered by plan cache evictions due to memory pressure? If so, how would I check for this?  I'm trying to see if there are any short-term remedies we can do, like upgrading the server RAM, until we deploy our application fixes.  The only other short-term option I can think of is to move some of our busiest databases to different server.

I believe you will see this symptom if you have a LOT of large query plans that are fighting for memory in order to compile (this has very little to do with running the query itself). To hit this, I suspect you are using an ORM or some kind of application that generates many unique but relatively complex queries. SQL Server could be under memory pressure because of things like large query operations, but on further thought it is more likely just that your system is configured with far less memory than it needs (either there is never enough memory to satisfy all of the queries you're trying to compile, or there are other processes on the box that are stealing memory from SQL Server).

You can take a look at what SQL Server is configured with using:

EXEC sp_configure 'max server memory';    -- max configured in MB

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'Total Server Memory (KB)',    -- max currently granted
    'Target Server Memory (KB)'    -- how much SQL Server wished it had
  );

You can identify the cached plans that required the most compile memory with the following Jonathan Kehayias query, adapted slightly:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (10) CompileTime_ms, CompileCPU_ms, CompileMemory_KB,
  qs.execution_count,
  qs.total_elapsed_time/1000.0 AS duration_ms,
  qs.total_worker_time/1000.0 as cputime_ms,
  (qs.total_elapsed_time/qs.execution_count)/1000.0 AS avg_duration_ms,
  (qs.total_worker_time/qs.execution_count)/1000.0 AS avg_cputime_ms,
  qs.max_elapsed_time/1000.0 AS max_duration_ms,
  qs.max_worker_time/1000.0 AS max_cputime_ms,
  SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
    (CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
     END - qs.statement_start_offset) / 2 + 1) AS StmtText,
  query_hash, query_plan_hash
FROM
(
  SELECT
    c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
    c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
    c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
    c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
    c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
    qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)
) AS tab
JOIN sys.dm_exec_query_stats AS qs ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileMemory_KB DESC
OPTION (RECOMPILE, MAXDOP 1);

You can see how the plan cache is being used with the following:

SELECT objtype, cacheobjtype,
    AVG(size_in_bytes*1.0)/1024.0/1024.0,
    MAX(size_in_bytes)/1024.0/1024.0,
    SUM(size_in_bytes)/1024.0/1024.0,
    COUNT(*)
FROM sys.dm_exec_cached_plans
GROUP BY GROUPING SETS ((),(objtype, cacheobjtype))
ORDER BY objtype, cacheobjtype;

When you are experiencing high semaphore waits, check to see if these query results vary significantly from during "normal" activity:

SELECT resource_semaphore_id, -- 0 = regular, 1 = "small query"
  pool_id,
  available_memory_kb,
  total_memory_kb,
  target_memory_kb
FROM sys.dm_exec_query_resource_semaphores;

SELECT StmtText = SUBSTRING(st.[text], (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1),
  r.start_time, r.[status], DB_NAME(r.database_id), r.wait_type,
  r.last_wait_type, r.total_elapsed_time, r.granted_query_memory,
  m.requested_memory_kb, m.granted_memory_kb, m.required_memory_kb,
  m.used_memory_kb
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_query_stats AS qs
ON r.plan_handle = qs.plan_handle
INNER JOIN sys.dm_exec_query_memory_grants AS m
ON r.request_id = m.request_id
AND r.plan_handle = m.plan_handle
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS st;

And you may also want to look and see how memory is distributed:

DBCC MEMORYSTATUS;

And there is some good information here about why you might be seeing a high number of compiles/recompiles (which will contribute to that wait):

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

http://technet.microsoft.com/en-us/library/cc293620.aspx

You can check for high compile/recompile counts using the following counters:

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'SQL Compilations/sec',
    'SQL Re-Compilations/sec'
  );

And you can check for internal memory pressure leading to evictions - non-zero counters here would indicate that something not good is going on with the plan cache:

SELECT * FROM sys.dm_os_memory_cache_clock_hands
  WHERE [type] IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP');

NOTE Most of these metrics don't have a magic "oh my gosh I need to panic or do something!" threshold. What you need to do is to take measurements during normal system activity, and determine where these thresholds are for your hardware, configuration and workload. When you panic do something is when two conditions are true:

the metrics vary significantly from normal values; and,
there is actually a performance problem occurring (like your CPU spikes) - but only if they are actually interfering with anything. Other than seeing the CPUs spike, are you seeing any other symptom? In other words, is the spike the symptom, or is the spike causing other symptoms? Would users of the system ever notice? A lot of people always go after their highest wait consumer, simply because it's the highest. Something is always going to be the highest wait consumer - you have to know that it's varying enough from normal activity that it indicates a problem or some significant change.

Optimize for ad hoc workloads is a great setting for 99% of the workloads out there, but it will not be very helpful in reducing compilation costs - it is aimed at reducing plan cache bloat by preventing a single-use plan from storing the whole plan until it's been executed twice. Even when you only store the stub in the plan cache, you still have to compile the full plan for the execution of the query. Perhaps what @Kahn meant to recommend was setting the database level parameterization to forced, which will potentially provide better plan re-use (but it really depends on how unique all of these high-cost queries are).

Also some good information in this white paper about plan caching and compilation.

The most typical reason by far I've seen those waits appear, are as a result of fragmented or insufficient indexes, and statistics that either have insufficient sample size, or are obsolete. This results in massive full table scans hogging up all the memory, which in turn produces a symptom we often see as RESOURCE_SEMAPHORE_QUERY_COMPILE.

The easiest way to verify this is to check if the queries run full table scans / index scans, when they should be doing index seeks. If you have a problem query with which you can reproduce the problem - it becomes very easy to diagnose and fix this.

I would check the indexes on the tables affected by those problem queries - ie. check index fragmentation, potential filtered indexes that aren't used, missing indexes you might want to create, etc. Also, update their statistics with FULLSCAN as soon as possible.

A good point to remember is that your problem table may not be the only one in need of this. For example, if you have a query which fetches data from 10 tables, the execution planner may occasionally show that it isn't using the index on table 1, but when you then check the index on table 1, it's actually ok. The query planner may resolve to fetch data on table 1 with a full table scan correctly, because a faulty / insufficient index on table 7 for instance, returned so much data that this would be the faster option. So diagnosing these can sometimes be tricky.

Also, if you have a lot of codebehind queries with just a few changes in variable values for instance, you might want to look at enabling optimization for ad hoc workloads. Basically what it does is store a stub of the compiled plan instead of the whole plan, saving up resources when you never quite get the exact same plans every time.


Memory-optimized table index size 10X bigger than data On one of our production servers, I found that index size is 10X times bigger than data size.
These servers and schemas are identical with a similar workload.
CREATE TABLE [dbo].[process_aggregation_close]
(
    [organization_id] [bigint] NOT NULL,
    [computer_id] [uniqueidentifier] NOT NULL,
    [process_id] [int] NOT NULL,
    .
    .
INDEX [ix_process_aggregation_memory_computer_id_process_id] NONCLUSTERED
(
    [organization_id] ASC,
    [computer_id] ASC,
    [process_id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

Tables are staging tables and they are involved in very frequent insert/delete operations.

Microsoft SQL Server 2017 CU9 Standard Edition.
All databases are in the full recovery mode. I do not think it can be related to checkpoint, since the tables are schema-durable only. (In any case, I tried a manual checkpoint with no effect).
These in-memory tables are staging and always insert and delete activities exists on them. I monitor five SQL Servers with similar load on them. This behavior occurs only on one.

The size difference is due to how row versioning in indexes and the garbage collector of in memory tables work.

Looking at the garbage collection process used for memory optimized tables:

  If there is no transactional activity after (for example) deleting a
  large number of rows and there is no memory pressure, the deleted rows
  will not be garbage collected until the transactional activity resumes
  or there is memory pressure

Okay, that makes sense for the indexes, but why is the data cleared while the indexes are not cleared?

My educated guess is that removing the row versioning from indexes is a different process than removing the overwritten data from the table. More on that here.

An example for this, with a query that inserts 1M rows and executes a full delete on a table that consists of the three columns you have in your question.

-- insert data into the tables
SET NOCOUNT ON;
DECLARE @I int =0;
WHILE @I < 1000000
BEGIN
    INSERT INTO dbo.MemOptimizedTable([organization_id], [computer_id], [process_id])
    VALUES (cast(@I as bigint) + 2147483647, NEWID(),@I) ;
    SET @I +=1;
END
-- Delete everything
 DELETE FROM  dbo.MemOptimizedTable;

SELECT object_id, memory_used_by_table_kb, memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats;

After running the query the first time

object_id   memory_used_by_table_kb memory_used_by_indexes_kb
1045578763          62501           58852

Running the query 3 times gives us this starting point where the index size has grown beyond the table size

object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           62501           78464

While the insert of 1M records is running again, the mem used by table grows

--Insert running
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           86715            88471

And grows

--Insert running
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           123628           104662

Until the insert is finished

--Insert done
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           62500            79543

The index grew, the memory used by the table was removed after the insert and remained the same. After this insert finishes, a process is triggered to remove the excess memory that was used by the table.

This is not by any means the complete explanation but could be a starting point.

What can we do?

According to this post there’s also no way to force garbage collection to occur.

You could op to

Do nothing and let SQL Server handle it
Drop and create the index to reset the memory usage (if you have another index on the table)
...

Commands to drop and create the index example

ALTER TABLE  dbo.table
       DROP INDEX [ix_process_aggregation_memory_computer_id_process_id];

ALTER TABLE  dbo.table
       ADD INDEX [ix_process_aggregation_memory_computer_id_process_id] ([organization_id] ASC,[computer_id] ASC, [process_id] ASC);

Create table statement of the table used in the example

 CREATE TABLE dbo.MemOptimizedTable(
  [organization_id] [bigint] PRIMARY KEY NONCLUSTERED NOT NULL,
    [computer_id] [uniqueidentifier] NOT NULL,
    [process_id] [int] NOT NULL,
INDEX [ix_process_aggregation_memory_computer_id_process_id] NONCLUSTERED
(
    [organization_id] ASC,
    [computer_id] ASC,
    [process_id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)
GO


Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server This is going to be a long story I'm afraid ;-(

I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files.

The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error:

  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXXXX". OLE DB
  provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXX"
  returned message "Unspecified error". (Microsoft SQL Server, Error:
  7303)

After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine.

Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder.

But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing is connected to the database engine with a windows AD account, also belonging to the administrator group. But when he tries to test the connection, it throwing the same error.

An other thing I tried is using the OPENROWSET, this also give an unspecified error. And yes I have set the "Ad hoc distributed query" using the next statement:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

So here I got my development box, where everything is working fine. But when I deploy to the test-server it has these connection-problems. Actually I don't know what to do or check anymore. I have been working and searching for several days now. Does anyone have some other suggestions I can try?

When you set up a linked server, you have the choice to re-use the credentials of the user, use a specific account or be anonymous.  When you tested it from the same machine, and SQL Server was running as a local account, it worked because it was able to re-use the admin privileges of you while you were logged-in.

To get it to work in a network scenario, things get tricky.  If you are using AD, you need to use AD (principalName) to configure the Kerberos trust between the SQL Server machine and the machine hosting the MSAccess file.  This usually takes some fiddling to get it right.  This TechNet article is long-winded, but contains a lot of config steps and suggestions: http://support.microsoft.com/kb/319723.

Also, you will need to set the server back to use the NETWORKING SERVICE account again so you can grant it permissions.  On the other machines, your SQL Server will be using a hidden account like [domain]\[MachineName]$.  If the MS Access DB is on the same machine as your SQL Server, you still need to grant (NTFS) read/write permissions on the MSAccess DB (file) to the accounts for the SQL Server process and to the network users.


If a Parallelism Exchange Event deadlock is victim-less, is it a problem? We're seeing a lot of these Intra-Query Parallel Thread Deadlocks in our Production environment (SQL Server 2012 SP2 - yes...I know...), however when looking at the Deadlock XML that has been captured via Extended Events, the victim-list is empty.

<victim-list />

The deadlocking appears to be between 4 threads, two with the WaitType="e_waitPipeNewRow" and two with the WaitType="e_waitPipeGetRow".

 <resource-list>
  <exchangeEvent id="Pipe13904cb620" WaitType="e_waitPipeNewRow" nodeId="19">
   <owner-list>
    <owner id="process4649868" />
   </owner-list>
   <waiter-list>
    <waiter id="process40eb498" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe30670d480" WaitType="e_waitPipeNewRow" nodeId="21">
   <owner-list>
    <owner id="process368ecf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process46a0cf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe13904cb4e0" WaitType="e_waitPipeGetRow" nodeId="19">
   <owner-list>
    <owner id="process40eb498" />
   </owner-list>
   <waiter-list>
    <waiter id="process368ecf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe4a106e060" WaitType="e_waitPipeGetRow" nodeId="21">
   <owner-list>
    <owner id="process46a0cf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process4649868" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>

So:

The Victim List is empty
The application running the query does not error and completes the query
As far as we can see, there is no obvious issue, other than that the graph is captured

Therefore, is this anything to worry about other than noise?

Edit: Thanks to Paul's answer, I can see where the issue likely occurs and appears to resolve itself with the tempdb spill.

I wouldn't be surprised if this is the way the deadlock graph looks when an intra-query parallel deadlock is resolved by an exchange spill (so there is no victim, except performance).
You could confirm this theory by capturing exchange spills and matching them up (or not) to the deadlock.
Writing exchange buffers to tempdb to resolve a deadlock is not ideal. Look to eliminate sequences of order-preserving operations in the execution plan (e.g. order-preserving exchanges feeding a parallel merge join). Unless it's not causing a noticeable performance problem, and you have other things to worry about.

Out of interest, is this problem likely to be exacerbated by high fragmentation/outdated statistics?

Fragmentation, no. Outdated Statistics: not in any specific sense I can think of, no. Of course unrepresentative stats are rarely a good thing generally.
The fundamental issue here is that parallelism works best when there are as few dependencies between threads as possible; preserved ordering introduces rather nasty dependencies. Things can easily get gummed up, and the only way to clear the logjam is to spill a bunch of rows held at exchanges to tempdb.

Note
Intra-query parallel deadlocks no longer generate xml graphs when the deadlock can be resolved with an exchange spill, following a fix released for Cumulative Update 10 for SQL Server 2017 and Cumulative Update 2 for SQL Server 2016 SP2.

To distinguish these non-critical, "self resolving by spill" deadlocks from more important deadlocks, some searching semantics can be applied to the Xdl structure.

The following SP will not work out of the box as it depends on ufn_ExtractSubstringsByPattern() however that method can be replaced with something that returns the distinct count directly.

ALTER view [Common].[DeadLockRecentHistoryView]
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose:  List history of recent deadlock events

    Warning:  The XML processing may hit a recursion limit (100), suggest using "option (maxrecursion 10000)".

    Xdl File:
        The SSMS deadlock file format .XDL format (xml) has changed with later versions of SQL Server.  This version tested with 2012.

    Ring Buffer issues:
        https://connect.microsoft.com/SQLServer/feedback/details/754115/xevents-system-health-does-not-catch-all-deadlocks
        https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

    Links:
        http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/
        https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/
        http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/
        http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx
        http://dba.stackexchange.com/questions/10644/deadlock-error-isnt-returning-the-deadlock-sql/10646#10646

    Modified    By           Description
    ----------  -----------  ------------------------------------------------------------------------------------------
    2014.10.29  crokusek     From Internet, http://stackoverflow.com/questions/19817951
    2015.05.05  crokusek     Improve so that the output is consumable by SSMS 2012 as "Open .xdl file"
    2015.05.22  crokusek     Remove special character for the cast to Xml (like '&')
    2017.08.03  crokusek     Abandon ring-buffer approach and use event log files.  Filter out internal deadlocks.
    2018.07.16  crokusek     Added field(s) like ProbablyHandledBySpill to help identify non-critical deadlocks.
  ---------------------------------------------------------------------------------------------------------------------*/
with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
    from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
   where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'
)
select top 10000
       EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as CreatedUtc,
       --(select TimePst from Common.ufn_ConvertUtcToPst(EventData.value('(event/@timestamp)[1]', 'datetime2(7)'))) as CreatedPst,
       DistinctSpidCount,
       HasExchangeEvent,
       IsVictimless,
       --
       -- If the deadlock contains Exchange Events and lists no victims, it probably occurred
       -- during execution of a single query that contained parallellism but got stuck due to
       -- ordering issues.   https://dba.stackexchange.com/questions/197779
       --
       -- These will not raise an exception to the caller and will complete by spilling to tempdb
       -- however they may run much slower than they would without the spill(s).
       --
       convert(bit, iif(DistinctSpidCount = 1 and HasExchangeEvent = 1 and IsVictimless = 1, 1, 0)) as ProbablyHandledBySpill,
       len(et.XdlFileText) as LenXdlFile,
       eddl.XdlFile as XdlFile
  from XmlDeadlockReports
 cross apply
     (
       select eventData.query('event/data/value/deadlock') as XdlFile
     ) eddl
 cross apply
     (
        select convert(nvarchar(max), eddl.XdlFile) as XdlFileText
     ) as et
 cross apply
     (
       select count(distinct Match) as DistinctSpidCount
         from common.ufn_ExtractSubstringsByPattern(et.XdlFileText, 'spid="%%"')
     ) spids
 cross apply
     (
       select convert(bit, iif(charindex('<exchangeEvent', et.XdlFileText) > 0, 1, 0)) as HasExchangeEvent,
              --
              convert(bit, iif(     charindex('<victim-list>', et.XdlFileText) = 0
                                and charindex('<victim-list/>', et.XdlFileText) > 0, 1, 0)) as IsVictimless
     ) as flags
 order by CreatedUtc desc
GO


Comfortable sqlplus interface? I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available.

What is a good replacement / extension for sqlplus? Could be a GUI or better (so it stays useful via SSH) a command line utility.

SQL*Plus is the main command line tool to operate with the Oracle Database.

but for example no "arrow-up" key for the previous history entry is available.

You're talking about sqlplusw the "GUI" tool - the oldschool sqlplus does support
arrow-up-bring-previous-statements feature.

  What is a good replacement / extension for sqlplus

Oracle's SQL Developer, a free GUI-based tool, is pretty neat.

If you're a vim addict, there's VoraX which is augments SQL*Plus very well.

  VoraX aims to be a simple, yet a powerful IDE for Oracle databases. Instead of reinventing the wheel, VoraX relies on the well-known SqlPLUS which is actually used to connect to the database, to execute SQL statements and to get the results back. However, this interaction is hidden from the user who just uses his/her Vim environment without knowing that, behind, a SqlPLUS process does all the work.

  The big advantage of this approach is that almost all SqlPLUS commands may be used without a problem. You can format columns, set pagination, autotrace queries etc.

  FEATURES

  99.9% of the sqlplus commands supported
  advanced code completion
  it runs on Windows and UNIX
  a nice results window with highlighting for errors and logging support
  a database explorer which may be used to browse the database objects in a hierarchical way
  support for editing and compiling PLSQL objects like packages, types etc.
  convenient key mappings for: describe object under cursor, execute current statement etc.
  connection profiles management (including securely storing of passwords)
  fuzzy search for database items
  offline search for Oracle html documentation

You should try out dbWatch. It is a tool for monitoring, reporting and administration across database platforms. It currently supports Oracle, SQL Server, MySQL, PostgreSQL, Sybase and Ingres.

One of the things included in dbWatch is SQL Worksheet which is a sqlplus like interface that also allows for cross database sqls, for example if you are looking for a table across all your databases or want to remove a user.

I work there and i suggest you try it out. (http://www.dbwatch.com/)Let us know what you think. There is a 10 day free full use license included by default, but we extend your test period if you need a bit more time to test it.

-Chris


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

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

I want to obtain 200 - 10 = 190

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

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

What is the syntax for this command in SQL Server?

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

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

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

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

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

GO
DROP TABLE #fl;

Why don't use the MAX and Min Function (Optional if you have Criteria use Where)

Eg. Select (Max(NumFieldName) - Min(NumFieldName)) AS Output FROM TableName


Writing Conditions in Left join sql I want to know why these queries give different results

    Select *  FROM Table1 T1
    Left Join MyTable MT
    ON MT.studentId = T1.StudentId
    AND T1.class = 7

    Select *  FROM Table1 T1
    Left Join MyTable MT
    ON MT.studentId = T1.StudentId
    WHERE T1.class = 7

The where clause is evaluated after the join. A left join B means all tuples  from A together with the matching tuples from B, if no tuples from B match null is the result. Given:

create table t1 (studentid int not null, class int not null);
create table t2 (studentid int not null);
insert into t1 values (1,1),(2,2);
insert into t2 values (1);

select * from t1 left join t2 on t1.studentid = t2.studentid;

STUDENTID   CLASS       STUDENTID
----------- ----------- -----------
      1           1           1
      2           2           -

i.e. there is no row in t2 that matches studentid 1 so a null is the result. For:

select * from t1
left join t2
    on t1.studentid = t2.studentid
   and t1.class = 1;

Nothing changes. For:

select * from t1
left join t2
    on t1.studentid = t2.studentid
where t1.class = 1;

on the other hand, the where clause is evaluated after the join so t1.studentid  2 is removed from the result.

Lets say T1 contains 1000 records ( 100 for each class 1 to 10 )

Select *  FROM Table1 T1
Left Join MyTable MT
ON MT.studentId = T1.StudentId
AND T1.class = 7

This Query : performs left join on only class 7 records of t1 and MT table and prints the entire T1 table

so if I have a class 8 student Id in MT table, it wont be reflected in the Left Join but I will get entire 1000 records from t1

Select *  FROM Table1 T1
Left Join MyTable MT
ON MT.studentId = T1.StudentId
WHERE T1.class = 7

This Query : performs left join of entire table t1 with MT and in the end due to Where Condition it will display only 100 records from class 7, but

so if I have a class 8 student Id in MT table, it would have been reflected in Left Join but not displayed due to Where Condition

Lastly the optimized query which I was looking for

Select *  FROM Table1 T1
Left Join MyTable MT
ON MT.studentId = T1.StudentId
AND T1.Class = 7
WHERE T1.class = 7

This query combines the impact of the above queries together.
It left Joins 100 records ( class 7 ) of t1 with MT table ( due to join condition )
and It only Displays only 100 records ( class 7 ) of resultant join due to Where condition.


Searching for a keyword across multiple columns I have a set of names of schools stored in each row in my schools table. the same table has four different category columns which store the names of the subjects they offer. I want to enable functionality where the user conducts a search on the table to find out all the names of the schools which teach a particular subject (for eg: physics). The name of the particular subject could be stored in any of the four subject columns. Please suggest a mysqli_/mysql_ query to conduct such a search or if you may so please, suggest a PHP script that would achieve the above functionality.

You could write a simple query using OR in WHERE clause and passing your search string.

SELECT school_name
FROM schools
WHERE Subject1 = 'Physics'
OR Subject2 = 'Physics'
OR Subject3 = 'Physics'
OR Subject4 = 'Physics'

You can parameterize the search string and pass on to this query.

SELECT school_name
FROM schools
WHERE 'Physics' IN (Subject1, Subject2, Subject3, Subject4);