Visualizing SQL Server Extended Events data Lately I've been exploring using Extended Events in SQL Server to help me benchmark and optimize various queries. So far, to view the event data I have been using the "Watch Live Data" feature in SSMS.

The problem that I am having is that it appears that the Live Events feature uses an internal buffer, which means that sometimes I need to execute a query several times to get its information to display in the window. I therefore have a two-part question to ask:

Is there a way to get around this delay in getting the events to display in the live feed? (I'm doing this on a local database so performance is not an issue)
Is the live feed the best way to visualize Extended Events data? Is there another tool either in SSMS or not that is better adapted to my use case?

UPDATE

As requested, here is the session:

CREATE EVENT SESSION [Simple Query Benchmarking] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_uint64]([duration],(1000))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Caveat: Much of the information provided below I learned strictly from going through two Pluralsight courses by Jonathan Keyhayias. Well worth the one month expense for the plus subscription to go through his two courses.

First just a few points of interest that I think will help (or at most be of interest):

When an Extended Event session is started it will have a portion of memory allocated to a buffer space to store data generated by the events of the session. In your session this is set to the default value of 4MB
There are multiple targets available to use. These targets are either synchronous or asynchronous in how they receive data. The two most commonly used targets being Target File and Ring Buffer are both asynchronous. BOL article here indicates what type each target is.
The MAX_DISPATCH_LATENCY is a configuration option that controls when event data is dispatched to the target(s). Dispatching only occurs for asynchronous targets. There are two conditions that will cause event data to be dispatched: (1) memory buffer for the session is full or (2) event data in the buffer exceeds the session's MAX_DISPATCH_LATENCY configured option.
When you open the Live Data Viewer it will attach an additional target to the event session called "streaming target". This will receive the live event stream as memory buffers are being dispatched. It actually will also change the dispatch latency associated with the session to 3 seconds in order to get near real time view of the session.

Now to specific points in your question:

  The problem that I am having is that it appears that the Live Events feature uses an internal buffer, which means that sometimes I need to execute a query several times to get its information to display in the window. I therefore have a two-part question to ask

I am not aware that it does this other than what I stated above. I would expect that the event was captured it just has not meet the thresholds required for it to be dispatched to your live data viewer. I tested this with the following query from AdventureWorks2012:

SELECT * FROM dbo.ErrorLog
WAITFOR DELAY '00:00:01' ;
GO

Using your event session configuration, with the exception that I am filtering to only capture data for AdventureWorks2012 database on my local instance, I can view the target data for this session and find the query was captured:

Executing this query one more time will finally cause it to get dispatched and the data viewer displays one event. Now if you actually want to see all the events that are displayed simply STOP the session and the buffer will get fully dispatched. I see this once I stop my session:

  1.Is there a way to get around this delay in getting the events to display in the live feed? (I'm doing this on a local database so performance is not an issue)

I had thought you could change the MAX_MEMORY to a lower value which would indicate a small buffer size to capture events. However the lowest value you can set this to in SQL Server 2012 is 200KB, which the query I used does not meet that limit to cause it to immediately be dispatched. Only thing I could do was at most execute a query that would cause the buffer to be reached and the previous events captured to be dispatched:

SELECT *
FROM Person.Person
ORDER BY EmailPromotion DESC;

  2.Is the live feed the best way to visualize Extended Events data? Is there another tool either in SSMS or not that is better adapted to my use case?

Not that I am aware of currently. I would suggest the best method of getting data out as soon as it occurs is to query the XML for the ring_buffer target and just shred that out. I can repeat the above example and as soon as I execute the query below I see the event.

-- Create XML variable to hold Target Data
DECLARE @target_data XML
SELECT  @target_data = CAST([t].[target_data] AS XML)
FROM    [sys].[dm_xe_sessions] AS s
JOIN    [sys].[dm_xe_session_targets] AS t
        ON [t].[event_session_address] = [s].[address]
WHERE   [s].[name] = N'Simple Query Benchmarking' AND
        [t].[target_name] = N'ring_buffer' ;

-- Return the full XML document
--SELECT @target_data;

--Shred XMl to get needed data
SELECT  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
    n.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
    n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM @target_data.nodes('RingBufferTarget/event[@name=''sql_batch_completed'']') AS q(n)


How can I delete repeated rows? I have a system that gathers RSS from many sites including UTF-8 based sites (non-english sites). I have tried to avoid repeated rows but I have repeats in my table.

RSS_items (id(auto_increment) PRIMARY KEY, url ,title, body)

Is there any way to delete the repeat? The problem is that sometimes this repeat happens and sometimes not. The first thing I think I must find those who are repeated, then delete the extra rows. But how ?

This would depend on which one of the repeat you want to keep. For this example, I will dedup the table based on url and title and keeping the first occurrence.

First thing to do: run these queries

SELECT COUNT(1) FROM RSS_items;

SELECT COUNT(1) dupcount_two,url,title FROM RSS_items
GROUP BY url,title HAVING COUNT(1) = 2 WITH ROLLUP;

SELECT COUNT(1) dupcount_morethantwo,url,title FROM RSS_items
GROUP BY url,title HAVING COUNT(1) > 2 WITH ROLLUP;

This will show total table count and how all (url,title) combinations that repeat twice and more than twice with the total for each at the bottom. If the sum of the repeats is more than 5% of the total:

CREATE TABLE RSS_items_URLTitle LIKE RSS_items;
INSERT INTO RSS_items_URLTitle (id,url,title)
    SELECT MIN(id),url,title
    FROM RSS_items GROUP BY url,title
    HAVING
;
DELETE B.*
    FROM RSS_items_URLTitle A
    INNER JOIN RSS_items B
    USING (url,title) WHERE A.id <> B.id
;
DROP TABLE RSS_items_URLTitle;

This method made be very slow if you do not have an index on (url,title)

Otherwise, run this

CREATE TABLE RSS_items_New LIKE RSS_items;
CREATE TABLE RSS_items_URLTitle LIKE RSS_items;
INSERT INTO RSS_items_URLTitle (id,url,title)
    SELECT MIN(id),url,title
    FROM RSS_items GROUP BY url,title
;
INSERT INTO RSS_items_New
    SELECT B.*
    FROM RSS_items_URLTitle A
    INNER JOIN RSS_items USING (id)
;
ALTER TABLE RSS_items RENAME RSS_items_Zap;
ALTER TABLE RSS_items_New RENAME RSS_items;
DROP TABLE RSS_items_URLTitle;
DROP TABLE RSS_items_Zap;

This method may be better since it only operates on the table id.

Please look over both methods. Try testing it on test databases with copies of the data before running anything.

If you want to keep the last occurrence of duplicates, replace MIN(id) with MAX(id).

Give it a Try !!!


SQL Server configuring both UCP and MDW on a single server I'm trying to configure both UCP and MDW on a SQL Server.

I have already setup UCP and it created [sysutility_mdw] database.

Now when i try to setup MDW it is trying to use sysutility_mdw database created in #1

Should I use same database for both UCP and MDW or should I create separate ones?

You should use the same database for UCP and MDW.

Actually, you can't use a different database. UCP is in fact a specialized collection set that uploads its data to a MDW database. The MDW database is unique for a SQL Server instance and Data Collector cannot upload different collection sets to different MDW databases.

The database name for UCP must be "sysutility_mdw" and cannot be changed, so if you decide to activate UCP, you will have to stick to that name. That's why I always advise naming the MDW database "sysutility_mdw", so you can activate UCP in the future. To be fair, very few people use it.


Database design that requires an index over two tables Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products may be in multiple categories):
Orders
id -- PRIMARY KEY
customer_id -- FOREIGN KEY (Customer - id)
product_id -> FOREIGN KEY (Product - id)

Products
id -- PRIMARY KEY

Categories
id -- PRIMARY KEY

Product_Categories
product_id -- FOREIGN KEY (Product - id)
category_id -- FOREIGN KEY (Category - id)

Data volume
Now, I have a fairly large amount of orders (~30M) and a reasonable number of categories (~1K) and customers (~10K). There is around 30K Products, with an average of 3 products by category. Products may be moved from a category to another occasionally (let's say a once per month shuffle)
Query tendencies
My problem is that I want to have the following type of query run fast: "Get all Orders for customer whose product is in Category C". That would look like:
SELECT * FROM Orders
JOIN Product_Categories ON Orders.product_id = Product_Categories.product_id
WHERE Orders.customer_id = X AND Product_Categories.category_id = Y

Indexing considerations
The best index I can think of is an index on customer_id in Orders, supported by a secondary index on Product_Categories.product_id. This leads to the following plan (not a real plan since the design I showed above is a very large simplification of the actual case):
 - Index Scan on Orders using index on customer_id ---> Returns ~10K Rows
 - 10K Joins done by Index Lookup on the product_id index of Product_Categories (MAIN TIME CONSUMER)
 - 9990 Rows Filtered Out.
 - 10 Rows Returned

I would like to have an index over (customer_id, category_id), but I haven't been able to find a way to do this. The best solutions I can think of is to add a column categories_id INTEGER[] and then either:

Add a GIN index using categories_id and customer_id with the inclusion in list operator.
Create 1000 Partial indexes on order_id.

In both cases, I would have to synchronize categories_id with the updates in the category ↔ product association tables, which is unfortunate.
Questions
My questions are:

Am I overthinking? Is the "filtering out 10k" rows not that bad of a
problem  and any solution I can think of will make the problem worse?
Am I missing something? Can I be efficient without changing my database schema?
Assuming I should change my database schema, what is the best way to do so?

If you have an index on product_categories (category_id), as well as the one you already have on orders (customer_id) then this type of query should be very fast.  You can do a highly specific index scan on each table separately, then hash join the results.

https://explain.depesz.com/s/JEpZ

If that isn't fast enough for you, or you can't get it to use such a plan even when you have indexes in place, then I'm afraid you will have to give us a lot more info, like the actual query plan including timing, and what time you hope to achieve.


Can't change root password: "The system cannot find the file specified." I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message:

  The system cannot find the file specified.

I'm using MySQL 5.1.70 (x86, 32-bit) on Windows 7 SP1 (64 bits). I've added MySQL's "bin" directory to my "Path" environment variable.

In the comments of the MySQL documentation, I read that I should have installed the service using the absolute path, so I stopped MySQL, and uninstalled it:

C:\Windows\system32>mysqld --remove
Service successfully removed.

Then I installed it again, using the absolute path this time:

C:\Windows\system32>C:\web\mysql-5.1.70\bin\mysqld.exe --install
Service successfully installed.

I started MySQL, and tried to change the password again:

C:\Windows\system32>mysqladmin -u root password Pe%8XiduwOqdZ<ZFE5!
The system cannot find the file specified.

I also tried with quotes:

C:\Windows\system32>mysqladmin -u root password 'Pe%8XiduwOqdZ<ZFE5!'
The system cannot find the file specified.

I also tried to change the current directory to MySQL's "bin" directory:

C:\Windows\system32>cd C:\web\mysql-5.1.70\bin

C:\web\mysql-5.1.70\bin>mysqladmin -u root password Pe%8XiduwOqdZ<ZFE5!
The system cannot find the file specified.

C:\web\mysql-5.1.70\bin>mysqladmin -u root password 'Pe%8XiduwOqdZ<ZFE5!'
The system cannot find the file specified.

What's wrong?

There are two techniques you can try:

TECHNIQUE #1

The comment from Michael - sqlbot already said it. Use double quotes

mysqladmin -u root password "Pe%8XiduwOqdZ<ZFE5!"

TECHNIQUE #2

root@localhost has no password. Try running this:

mysql -uroot -e"SET SQL_LOG_BIN = 0; SET PASSWORD = PASSWORD('mynewpassword');"

This should do it as well.

CAVEAT FOR MySQL 5.6 users : TECHNIQUE #2 will not work in Linux for MySQL 5.6. There is a separate file with the temp password for root@localhost ( ${HOME}/.mysql_secret ). You login with that password and change the password from there.

Give it a Try !!!


Check if a user exists in a SQL Server database I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database.

This is what I have tested:

USE [MyDatabase]
GO

IF NOT EXISTS (SELECT name
                FROM [sys].[server_principals]
                WHERE name = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool]
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO

But, this code SELECT name FROM [sys].[server_principals] doesn't return if that user exists in MyDatabase.

How can I check if an user exists in MyDatabase?

Use sys.database_principals instead of sys.server_principals.

So the final query would look like this (accounting for the user filter):

USE [MyDatabase]
GO

IF NOT EXISTS (SELECT [name]
                FROM [sys].[database_principals]
                WHERE [type] = N'S' AND [name] = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool]
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO


Good strategy to replicate MySQL data I'm working on MySQL database, well i have a master instance and slaves, and i need to add a new slave to Master. I adopt a simple strategy to do so: Stop slave, import data to new slave, and finally start new slave.

Well i get a dump using mysqldump, and import data even i get lot of lock. Some queries have the state update, so i did not know if it is a innodb lock or input/ouput error.

Finally, i imported the hole of data, but i suspect later that the binary log was missing on master, because the rotation log was for 1 day.

My question is how to do a good replication master->slave?

For setting a good Master- Slave Replication please visit this link What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

You can read it's a good answer.


SQL Profiler for Mac OS Is there a SQL profiler MSSSQL that can be installed and run on Mac? It doesn't matter if it is a paid version as long as it can run on my Mac OS. Thanks.

No, but here's a few options:

Run a server-side trace. You don't get the Profiler GUI, but the good news is that this kind of tracing is faster. (Heck, I recommend this to folks even when they have Windows on the desktop.) SQL Server Central has a good Stairway to Server-side Tracing. Server-side traces can output to file or to table - I'd caution against writing the trace data into a table on the same server that you're monitoring because that'll have a performance impact.

Run Profiler in a VM. After all, if you're managing SQL Server, you probably need SQL Server Management Studio anyway, and that's still Windows-only. (That's how I manage SQL Server personally - I'm a Microsoft Certified Master of SQL Server, and I've been using Macs since the mid-2000s.)

Run sp_BlitzCache. The open source sp_BlitzCache analyzes the most resource-intensive queries in your plan cache - without starting a heavy-overhead trace or XE session. SQL Server is already gathering this data for you on every supported version/edition. Yes, right now, even as you're reading advice on a web site. You can run it from anything app runs T-SQL, like Microsoft's new cross-platform SQL Operations Studio. (Disclaimer: I'm one of the sp_BlitzCache authors.)

Use Extended Events instead. (This isn't really a solution, but I know someone's going to suggest it, so might as well get it out of the way. The Extended Events people are like vegans, crossfitters, and atheists: they can't wait to tell you about their religion.) Extended Events is the replacement for Profiler, but the thing is, if you're just getting started, you're going to want to use SSMS's excellent wizard for setting up a new XE session - meaning, you still need SSMS. There's also a "Profiler" extension for Azure Data Studio, but...that's actually Extended Events.

Get a third party monitoring tool. If you need to know what's happening on your SQL Server on a regular basis, this is usually more lightweight than running Profiler. Plus, they have way more smarts built in to tell you what's been happening.


Installing SQL Server 2000 x86 Enterprise on Windows 2003 x64 I have inherited (lucky me) a very old accounting system which will be retired over the next 18 months.
In the meantime, for various reasons, I need to upgrade the DB server.

It's currently running SQL Server 2000 Sp4 Enterprise on Windows 2000 Advanced Server.

The only supported OS I can get to without upgrading the whole application (too costly) is Windows 2003 x64.

I plan to put it on a VMware server, Windows 2003 x64 Enterprise Edition, with SQL Server 2000 SP4 Enterprise Edition x86. The virtual server will have 8GB Ram allocated to it and 4 vCPUs.

Is Sql Server 2000 x86 on Win 2k3 x64able to access the full 8GB of the OS (using PAE or similar?)

Yes, SQL Server 2000 x86 can use AWE to buffer data pages above the 4GB limit. See How to configure SQL Server to use more than 2 GB of physical memory:

  sp_configure 'show advanced options', 1
  RECONFIGURE
  GO
  sp_configure 'awe enabled', 1
  RECONFIGURE
  GO
  sp_configure 'max server memory', ...
  RECONFIGURE
  GO

The article also explains how to enable /PAE in boot.ini, but if your OS is amd64 already you don't need it since the OS can already see the entire memory. So all you need is to enable AWE on the x86 SQL Server 2000 instance.


Very fast individual queries but when joined super slow I've got query 1:

select department_id from USER_ACCESS
  where USER_ACCESS.USER_ID = 13170
  AND USER_ACCESS.MANAGEMENT = 1

Takes about 0.475 seconds, which is not a lot for the data set I m trying to build. Basically USER_ACCESS consists of this structure:

USER_ID | DEPARTMENT_ID | LOCATION_ID | MANAGEMENT | SENIOR_MANAGEMENT | DIRECTOR |

It's basically a fine grained control over the actions in the system, whether a user has the authority to do something for a given department/location or both or sometimes if you're senior management and which department it is as long as you're tied to the location you're good to do whatever you want to do.

Now there is query 2:

select review.department_id from review
inner join sale on sale.department_id = review.department_id
where review.year_id = 10301

which takes about 0.136 seconds, however when I join these two on the department_id the query takes about 7 seconds.

I tried putting on or the other as a subquery, I also tried using WITH clause but it all turned out more less the same performance-wise.

To add more caveats, the USER_ACCESS has multiple department_id, so query 1 could potentially return many of the same department_id, also the same case goes for review it could return many duplicate department_id.

When I use distinct(department_id) on both queries I get slightly better performance but not by a lot, my question is why are they executing so slow when they are joined and other thing is how can I make em faster?

Sorry it was a such a dumb thing to ask, but again if this is a dumb answer please feel free to delete it. I've used the intersect instead of inner joining the two queries as subqueries.


Are non 'copy-only' full backups safe to recover from? I've recently realized the difference between a full backup and a 'Copy-only' full backup and I'm wondering if the backups I'm taking on a weekly and daily basis are safe to recover from.

This article suggests that differential backups can only be traced back to a 'parent' (if that is the correct word) full backup. To me this sounds like a DB with FULL recovery mode allows for a point-in-time recovery to the last full backup?

In any case. I'm currently taking a full backup on a daily basis and no differential backups. Some of the databases being backed up have a recovery mode of simple, some full and some are bulked-logged. However, there is no need to do point in time restore on any of them at this point (These databases are updated once a day via vendor dumps). I'm also not taking any transaction log backups of the full-recovery mode databases for now (because I don't know how to do this automatically yet I'm watching them manually until they start growing faster than they currently do).

Am I right in thinking that a database can be restored from a full-backup at the time that full-backup was taken, regardless of the recovery model and regardless if there is a log backup or not?

However, there is no need to do point in time restore on any of them at this point.

If this is the case you MUST put database in simple recovery mode and this will take care of the transaction logs. When database is in simple recovery mode you can take two types of backup
1. Full
2. Differential

Depending on how much data you can loose you can take daily full backup and then may be differential backup twice or 4 times a day just to make sure you loose as less data as possible. When you restore from backup you would have to restore the latest full backup and the latest differential backup taken after the latest full backup. You cannot take transaction log backup in simple recovery model SQL Server will itself manage your transaction log truncation.

Please read Understanding Logging and Recovery in SQL Server

  Am I right in thinking that a database can be restored from a full-backup at the time that full-backup was taken

Yes you are absolutely correct. Please read Understanding Backups in SQL Server


Error when passing multiple values I have below stored procedure..

it only works when I pass single value like "BH" but not when passing multiple values like "AE", "BH", "US", "FR"

CALL sp_populate_memo_country_companies("BH")

returns 5 rows

CALL sp_populate_memo_country_companies("BH", "US")

throw error:

  1064 - You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near '")' at line 1

here is the stored procedure:

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_populate_memo_country_companies`(IN param_country varchar(255))
BEGIN
    SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
    FROM locations
    JOIN country ON country.country_code_alpha2 = locations.country_code
    JOIN companies ON companies.company_id = locations.company_id
    LEFT JOIN payroll ON payroll.location_id = locations.location_id
    WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
    GROUP BY locations.location_id
    ORDER BY companies.company_name;
END

Kindly help...

This is one (of many) situation where Stored Routines are less useful than one would like.

A possible workaround:  Build a VIEW without the IN clause, then use the IN clause when fetching from the VIEW.


Secondary composite-key pages access we have this relation
Store: [SID, City, EID]
Employee: [EID, Name, Salary, SID]
Article: [AID, Name, Producer, Price]
Inventory: [AID, SID, Count]
Invoice: [IID, SID, Customer]
Item: [IID, SID, AID, Count]

Store.EID is a foreign key referencing Employee.EID and designates the store manager. Employee.SID
is a foreign key referencing Store.SID and describes in which store this employee is employed. The
inventory table stores (with foreign keys) how many articles (AID) are in stock at which store (SID). An
invoice is generated at a store (SID) and consists of multiple items, where each item is composed of an
article (AID) and a count.

The Employee table contains 20 000 employees, of which exactly two tuples t into one page. Each
employee has a (uniformly distributed) salary between 20 000 and 100 000. There are 10 stores, each
with the same amount of employees. The Employee table has two secondary composite-key indices on
(Salary,EID) and (EID,Salary). Both indices are B+ trees with a height of h and each leaf can store
5 references to pages.

The question is
Which index requires less page accesses for the query select * from Employee where EID = 5
and Salary > 60000?

So I know that the (EID, Salary) is better to use here, since the EID is unique and it will go to the EID = 5 and then find his salary, however, I find it a little bit confusing because actually, we have one access to the EID 5 then the condition will be checked and we will either get this employee or not.
So how can I calculate the costs of each in order to compare between them?

Since the index (EID,Salary) has a height of h, the PK on EID must have height <=h.  If this is a clustered index, then for <= h LIOs (page reads) the PK can satisfy the query.

Then assuming the PK is not clustered, using the PK costs h or h + 1 LIOs.

  condition will be checked and we will either get this employee or not. So how can I calculate the costs of each in order to compare between them?

The probability that the salary >60000 is about 0.5, so you can calculate an expected cost.  Using the index on (EID,Salary) has an expected cost of
0.5 * h + 0.5 * (h+1) = h + 0.5 LIOs.

Using the (Salary,EID) index costs h LIOs to seek to the first row with Salary >60000, and then you can scan across the leaf pages to find EID=5, which will require reading (0 -.05] of the total leaf pages.  If Employee has 2 rows per page the (Salary, EID) index would have, perhaps, 8 rows per page.  So you scan, on average, .25 of the rows or (20,000/8)/4=625.  So that would cost, on average 625+h LIOs.


profile name not valid when creating SQL server 2014 availability group I tried creating an availability group but keep encountering this error during the validation checks

  Create failed for Database 'HADR Seeding Test
  2d074a6f-c035-4046-ae4e-045436ca56c5'.

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  profile name is not valid (Microsoft SQL Server, Error: 14607)

As @Nic rightly suggested, the secondary replica had a trigger to send emails on CREATE DATABASE statements which was set to use an incorrect profile name. I corrected that and validation checks completed successfully.


Error in setting a value in sql server trigger I'm trying to make a trigger that checks which staff member have the least amount of assigned tasks, before adding the task, and assign it to that member.
What I came up with was something like this:

CREATE TRIGGER trgInsteadOfInsert ON helpCase
INSTEAD OF Insert
AS
declare
 @helpCaseId int,
 @problemTypeId int,
 @problemDescribtion varchar(200) ,
 @staffId int,
 @problemSolution varchar(200),
 @operatingSystemTypeId int,
 @applicationTypeId int,
 @helpCaseOpenDate datetime,
 @helpCaseclosedDate datetime;

select @helpCaseId=i.helpCaseId from inserted i;
select @problemTypeId=i.problemTypeId from inserted i;
select @problemDescribtion=i.problemDescribtion from inserted i;

set @staffId = (select Min(ID) frOM (SELECT staffId as ID, COUNT (*) FROM helpCase where helpCaseClosedDate IS NULL GROUP BY staffId)) ;

select @problemSolution=i.problemSolution from inserted i;
select @operatingSystemTypeId=i.operatingSystemTypeId from inserted i;

select @helpCaseOpenDate=i.helpCaseOpenDate from inserted i;
select @helpCaseclosedDate=i.helpCaseclosedDate from inserted i;

BEGIN

Insert into helpCase values(@helpCaseId,@problemTypeId,@problemDescribtion,@staffId,@problemSolution,@operatingSystemTypeId,@helpCaseOpenDate,@helpCaseclosedDate);
END

but I'm getting the following error for the line that I'm setting the value of @staffId in :

Incorrect syntax near ')'. Expecting AS,ID or QOUTED_ID.

Any idea what should I do!?

You have a derived table in the from clause without an alias.

This will give you the same error:

select *
from (
     select *
     from YourTable
     );

Add an alias to the derived table.

select *
from (
     select *
     from YourTable
     ) as T;

Note: Your trigger will fail if you insert more than one row at a time. The trigger is executed once per insert statement, not once per row.


Did the Change Tracking internals change from SQL Server 2008 to 2012? In trouble shooting an issue with synchronizing disconnected devices with a central database server, we are experiencing a problem after upgrading to SQL Server 2012 on the server. It appears that the CHANGE_TRACKING_MIN_VALID_VERSION is returning a value 1 higher than it should (or at least than it did prior to the upgrade.)

I have been working thru Arshad Ali's great walk thru example of how to set up a simple example.

I have run the scripts from #1 thru #5 to insert, delete, and update a row in the Employee table in both a SQL Server 2008 and a 2012 environment.

In 2008, the following statement returns a 0:

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Employee'))

In 2012, it returns a 1.

In working thru a few more scripts (6-8) in the tests, I set the retention period to 1 minute to hopefully force a cleanup action.  I left for the day and apparently it ran overnight.

In the 2008 instance, the CHANGE_TRACKING_CURRENT_VERSION and the CHANGE_TRACKING_MIN_VALID_VERSION are equal (11).  In the 2012 instance, the CHANGE_TRACKING_MIN_VALID_VERSION is one higher (12) than the CHANGE_TRACKING_CURRENT_VERSION (11).  This could have an impact to the synchronization process when a database is idle for extended periods of time.  And we have found that process could get caught in a loop, especially when the following test is performed to determine if a re-initialization, as opposed to synchronization, is required:

IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Employee')) > @sync_last_received_anchor
       RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''...

Has anyone else experienced this change in behavior? Does anyone have an explanation?

One doesn’t use the min_valid_version to track the changes. This is only used to validate if your client has to be reinitialized, if the metadata has been cleaned up before client could consume the changes.

CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)

Obtains the minimum version that is valid for use in obtaining change tracking information from the specified table when you are using the CHANGETABLE function.

Min_valid_version changes with the cleanup version and doesn’t depend on the changes to the user table. Every time cleanup thread runs there could be an update to min_valid_version irrespective of data changes.

Previous to 2012, min_valid_version was marked same as cleanup version, when in fact it should be one more than the cleanup version as metadata for that version has already been cleaned up. In 2012 that is what they changed to make sure they update the right min_valid_version.

One should not be tracking change using min_valid_version, instead should be saving the last_sync_version after every sync and call the CHANGETABLE to enumerate the changes after the last sync version.

By design - Min valid version changes with the cleanup version and doesn’t depend on the changes to the user table. Every time cleanup thread runs there could be an update to min valid version irrespective of data changes.

Resolve - Change procedure to use 'current_version' instead of 'min_valid_version'


Automatic insertion of calculated values to another I am creating an application, so I need trigger or function that will help automatically enter data in the database as data is entered into the system considering the other table's primary key.

I currently have AddItems, Sales and Products tables. The AddItems table is where all the invoice data is stored. What I need is to be able to add the quantity of products together according to the Item_Code. Once the sum is found the data should be stored on the Products table. This should happen every time a product product is added in the AddItems table.

AddItems

Item_ID (PK, IDENTITY)
Date
Supplier_Name
Track_NO
Quantity
Item_Code
Item_Description
Vat
Amount
Total_Amount

Products

Product_Id (Identity)
Item_Code (PK)
Item_Description
Available_Quantity

Query I managed to come up with needs to be manually ran to insert the values. Below is the statement used.

INSERT INTO Products
(Item_Code, Item_Description, Available_Quantity)
SELECT Item_Code, Item_Description,
    Sum(Quantity)
FROM AddItems
Group By Item_Code, Item_Description;

(Asked the same question but i couldn't merge my account with the other)

I've reduced your table schema in this way:

CREATE TABLE AddItems
(
Item_ID int IDENTITY PRIMARY KEY,
Quantity int NOT NULL,
Item_Code varchar(10) NOT NULL,
Item_Description varchar(100) NOT NULL
);

CREATE TABLE Products
(
Product_Id int IDENTITY PRIMARY KEY,
Item_Code varchar(10) NOT NULL,
Item_Description varchar(100) NOT NULL,
Available_Quantity int NOT NULL DEFAULT(0)
);

You can add a trigger like next one:

CREATE TRIGGER trg_AddItems_Insert
ON AddItems
FOR INSERT
AS
BEGIN

    /* update existing products */
    UPDATE   p
    SET      Available_Quantity = Available_Quantity + Qty
    FROM     Products p
    JOIN     (SELECT   Item_code, SUM(Quantity) Qty
              FROM     inserted
              GROUP BY Item_Code) s
    ON       p.Item_Code = s.Item_Code;

    /* insert new products */
    INSERT INTO Products
    (Item_Code, Item_Description, Available_Quantity)
    SELECT   i.Item_Code, i.Item_Description, Sum(Quantity)
    FROM     inserted i
    WHERE NOT EXISTS(SELECT 1
                     FROM Products
                     WHERE Item_Code = i.Item_Code)
    GROUP BY i.Item_Code, i.Item_Description;

END
GO

This solution assumes AddItems table won't be modified or deleted, it is only used to add new rows. If some user can UPDATE or DELETE rows, you should consider either:

Add one trigger for DELETE and another for UPDATE.
Use stored procedures to INSERT/UPDATE/DELETE rows.

Now, let me insert some values:

INSERT INTO AddItems VALUES
(10, 'AAA', 'PRODUCT A'),
(5,  'AAA', 'PRODUCT A'),
(7,  'CCC', 'PRODUCT C'),
(1,  'BBB', 'PRODUCT B');

INSERT INTO AddItems VALUES
(25, 'AAA', 'PRODUCT A'),
(10, 'BBB', 'PRODUCT B'),
(3,  'CCC', 'PRODUCT C'),
(5,  'CCC', 'PRODUCT C');

And this is the final result:

SELECT * FROM PRODUCTS;
GO

Product_Id | Item_Code | Item_Description | Available_Quantity
---------: | :-------- | :--------------- | -----------------:
         1 | AAA       | PRODUCT A        |                 40
         2 | BBB       | PRODUCT B        |                 11
         3 | CCC       | PRODUCT C        |                 15

db<>fiddle here


Provides Oracle's database in-memory? I'm trying to find out, if Oracle's newest database release 12c comes per default as an in-memory database or does it use TimesTen for this feature?
According to Wikipedia (Oracle Database) it "utilizes TimesTen technology" for "In-Memory Database Cache".
Are those two different databases or is the one like a feature of the other?

The new Oracle 12c In-memory database feature is not the same as TimesTen. It implements a column-store based storage in memory in addition to the traditional row-based format on disk. So your data is held in BOTH formats simultaneously. You choose which tables you want to store in memory. This can allow immense improvement in mixed-workload databases.

It is an extra cost option, not included free even with Enterprise edition. The rationale is that if you are maintaining a separate datamart for your analytics, you could instead do the analytics in real-time right on your OLTP system with the In-memory feature and eliminate the need for a datamart environment and its cost (not to mention all the ETL work you have to do to maintain a datamart).

See: http://oracle-base.com/articles/12c/in-memory-column-store-12cr1.php

A short video on it: http://www.youtube.com/watch?v=8Yf1ghec0gU

A longer video on it: http://www.youtube.com/watch?v=CjkPUg1m6PA


Parent and Child Relationship with the Same Foreign Table Business Rules (I realize these rules are a little abnormal):
A Person (student) can attend multiple schools. Once one or more Schools are chosen, the student can pick one or more degrees at each school. Once a degree is chosen, the Student has an option of choosing to participate in many DegreeSpecificGroups, Courses, and OptionalTrips.

I have 4 tables:

Table Person
Table School
Table Degree
Table DegreeSpecificGroup
Table Course
Table OptionalTrip

Assume that each table has a need for a unique set of properties for each Person. However, I am trying to keep this simple. My question is - where should I keep the relationships with the Person table? Do I make a relationship table for each combination - ie: PersonSchool, PersonDegree, PersonCourse, PersonOptionalTrip...?

I am having troubles convincing myself to do this because it contradicts my hierarchical thinking - IE: you cannot attend an OptionalTrip unless you are enrolled in the School and have selected a Degree. Half of me wants to keep all relationship information in the highest relationship level (SchoolPerson) - but of course that table will become bloated and unmanageable. What is the proper way to handle these multi tiered relationships?

I have looked at some ER diagrams to see some examples of other diagrams but they do not have the level of detail I am looking at

You have a Person, who is attending a School? They are studying for a Degree? A Degree will consist of 1 -> many Courses. A Course may be on many Degree programmes (e.g. Foundation Maths)?

The totally general case is that 1 Person can attend 1 or more schools studying for 1 or more Degrees in each school at any one time - is this possible? Some people may be repeating one course but have been allowed to advance, contingent on passing an exam elsewhere?

So, in order to be completely general, you want a Person table, a Degree table and a School table - with Person_Degree being your joining table between Person and Degree and Person and ultimately School. If a Person can only be doing 1 Degree in 1 School at any time, then you can simplify.

You then have a Degree_Course table which will have the joins between Degrees and Courses and a Course table.

Some Degrees can be multi-School - then you'll just have to have a further joining table. However, remember the YAGNI principle - if you don't have a current need, don't include it in your design. This can be difficult to judge - we want our systems to be flexible, but I'm with Einstein on this - make it as simple as necessary but no simpler!

Before a first take on the tables themselves - you could do worse than having a quick look here - you might get some ideas (and I might have even overlooked something! :-) )

Two things to bear in mind - one is that DDL (Data Definition Language) is quite powerful in its own right - right there in the database structure is a lot of "intelligence" - i.e. you can't have a degree without a school or a course without a school! The various other UNIQUE, PRIMARY KEY and FOREIGN KEY constraints enforce other logical rules about the system - rules you would otherwise have to spend a lot of time enforcing in your app!

Finally, a couple of words about table names.

I always use singular names for tables - they are like a class definition and/or a collection - (for me it is a singular concept - you might disagree). As an aside, you have tables in which you might want to have only 1 or even 0 records (think nuclear power plant system with a table called "catastrophic_meltdown"). In any case, choose one convention and stick to it.

Furthermore, my table_names_might_appear_long! :-) It can be greatly helpful when debugging to have meaningful table names - code spends >> 99% of its time in maintenance, so the extra typing is a (very) small price to pay for helpful error messages when things go wrong - and they will, no matter how good a programmer you are!

In PostgreSQL, I would do it like this (should translate reasonably well to other systems - check your documentation for details!) - note that I haven't dealt with semesters/trimesters here - adding a semester field to the degree and course tables might be necessary - again, this depends on your system requirements.

CREATE TABLE person
(
  person_id SERIAL,
  CONSTRAINT person_pk PRIMARY KEY (person_id),

  person_first_name VARCHAR(25) NOT NULL,
  person_last_name  VARCHAR(50) NOT NULL

  --
  -- .. other person stuff... address, phone &c.
  -- .. could make f_name + l_name UNIQUE?
);

CREATE TABLE school
(
  school_id SERIAL,
  CONSTRAINT school_pk PRIMARY KEY (school_id),
  school_name VARCHAR(50) NOT NULL

  -- other school stuff
  -- Tel., address... &c.
  --
);

CREATE TABLE degree
(
  degree_id SERIAL,
  CONSTRAINT degree_pk PRIMARY KEY (degree_id),
  degree_name VARCHAR(50) NOT NULL,
  degree_code VARCHAR(10) NOT NULL,

  degree_school_id INTEGER, -- no "NOT NULL, since it is an FK field - see below

  CONSTRAINT degree_school_fk FOREIGN KEY (degree_school_id) REFERENCES school (school_id)

  -- other degree stuff - faculty, prerequisites (another table?)

);

CREATE TABLE course
(
  course_id SERIAL,
  CONSTRAINT course_pk PRIMARY KEY (course_id),
  course_name VARCHAR(50) NOT NULL,
  course_code VARCHAR(10) NOT NULL,

  course_school_id INTEGER,
  CONSTRAINT course_school_fk FOREIGN KEY (course_school_id) REFERENCES school (school_id)

  -- other stuff - possibly a course_prerequisites table
);

CREATE TABLE degree_course
(
  degree_course_id SERIAL,
  CONSTRAINT degree_course_pk PRIMARY KEY (degree_course_id),
  degree_course_degree_id INTEGER NOT NULL,
  degree_course_course_id INTEGER NOT NULL,

  CONSTRAINT degree_course_degree_course_uq UNIQUE (degree_course_degree_id, degree_course_course_id),  -- prevent same entry twice

  CONSTRAINT degree_course_degree_fk FOREIGN KEY (degree_course_degree_id) REFERENCES degree (degree_id),
  CONSTRAINT degree_course_course_fk FOREIGN KEY (degree_course_course_id) REFERENCES course (course_id)
);

CREATE TABLE prerequisite
(
  prerequisite_id SERIAL,
  CONSTRAINT prerequisite_pk PRIMARY KEY (prerequisite_id),
  main_course_id INTEGER,
  preq_course_id INTEGER,
  CONSTRAINT prerequisite_uq UNIQUE (main_course_id, preq_course_id)  -- prevent same entry twice

  -- other info...
  --
);

CREATE TABLE person_degree
(
  person_degree_id SERIAL,
  CONSTRAINT person_degree_pk PRIMARY KEY (person_degree_id),

  person_id INTEGER NOT NULL,
  degree_id INTEGER NOT NULL

  -- various FOREIGN KEY and UNIQUE constraints to be added here!
);

Similar principles apply to your optionaltrip and degreespecific group tables - if you're having further issues, post back!


select
  round(
    (cast(current_timestamp as date) - cast(<other_timestamp> as date))
    * 24 * 60
  ) as diff_minutes
from <some_table>;

This is what I used to calculate the difference between the current timestamp and a heart beat table entry for latency monitoring.


Mongo WriteConflictException in log file I am experiencing ALOT of this WriteConflictException in my mongo log file, this does not seem to affect the application, because I am not seeing any special errors and everything seems to work fine.

Should I be concerned about this?

> <Time> D WRITE    [conn1397953] Caught
> WriteConflictException doing plan execution on ***, attempt: 1
> retrying 2018-11-20T06:35:39.612+0000 D WRITE    [conn1397956] Caught
> WriteConflictException doing plan execution on ***, attempt: 1
> retrying 2018-11-20T06:35:40.149+0000 D WRITE    [conn961669] Caught
> WriteConflictException doing plan execution on ***,
> attempt: 1 retrying 2018-11-20T06:35:40.149+0000 D WRITE
> [conn961669] Caught WriteConflictException doing findAndModify on
> ***, attempt: 0 retrying

Thanks

this does not seem to affect the application, because I am not seeing any special errors and everything seems to work fine.

Write conflict exceptions (WCEs) are an indication from the WiredTiger storage engine that multiple internal writers are trying to update the same data. WiredTiger uses optimistic concurrency control (more specifically Multi-Version Concurrency Control aka MVCC) rather than pessimistic (or lock-based) document updates.

WCEs are an expected (and mostly internal) aspect of this approach: when the storage engine detects conflicts between two concurrent write operations, one will incur a write conflict and be transparently retried. The WCEs are logged for diagnostic purposes but are not exceptions that your application needs to handle.

  Should I be concerned about this?

A small number of write conflict exceptions can be expected and should not have any impact aside from some possible (and generally negligible) latency for internal retries. Your redacted log lines show low retries and do not appear to be concerning in terms of WCEs.

If you are seeing updates with tens or hundreds of write conflict exceptions (or significantly slow execution), it would be worth reviewing the data model and updates in question for potential improvements in efficiency. For example, if a high number of write conflicts was being logged when incrementing a counter in a popular document, you might consider coalescing  N updates of  {$inc: 1} into  a single update of {$inc: N}.

The MongoDB Concurrency FAQ is also worth reviewing for more information.


Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures? If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right?
You can call a function inside a procedure
create or replace procedure P
    a_variable_name number;
begin
    a_variable := a_function('a parameter');
end;
/

And i imagine you'd have to pass the result of your procedure into the function. Like
if X procedure executed correctly, then do nothing
if Y error, do the Y exception handling

Is such a thing possible? If not, how do oracle users handle writing 10 different procedures that all use the same exception handling? Like, are there any tips and tricks besides just copying and pasting those exceptions and hoping you'll never have to change all 10 of them if you have to change one thing?

Well, you could have a procedure that checked for various values of sqlcode or sqlerrm and performed whatever actions you have in mind based on those, and called that procedure from your exception handler passing the filename and directory object name. However, I don't think that would be a neat solution.
If the problem is trying to reproduce the (quite old) Oracle Base approach for handling utl_file exceptions in multiple procedures, don't do that. It just copies every single exception from the manual and dumps them all into a monumental end-of-procedure exception handler, regardless of context.
When you open a file, you should use an exception handler to ensure that it opened OK, otherwise you can report that you couldn't open file x in folder y. For example (from www.williamrobertson.net/documents/refcursor-to-csv.shtml):
begin
    l_file :=
        utl_file.fopen
        ( filename => p_filename
        , location => p_directory
        , open_mode => 'w'
        , max_linesize => 4000 );
exception
    when utl_file.invalid_operation then
        raise_application_error(-20007, 'File '''||p_filename||''' could not be opened in directory '||p_directory||' ('||k_dir_path||')', TRUE);
    when utl_file.invalid_path then
        raise_application_error(-20008, 'File location '||p_directory||' ('||k_dir_path||') is invalid.', TRUE);
    when utl_file.invalid_filename then
        raise_application_error(-20009, 'Filename '''||p_filename||''' is invalid.', TRUE);
end;

Similarly, when you write to a file you could use exception handlers to ensure that write errors are reported clearly with appropriate details.
Or, centralise all CSV writing operations in a single package like the one I linked to, where you just pass in a refcursor and the file details and it does the rest.


Benefits of droping a temporary table What are the benefits (if any) of dropping a temporary table in SQL Server?

eg:

DROP TABLE #temp;

Since temporary tables are automatically dropped by the server this statement would seem to be non-functional.

I typically drop #temp tables explicitly just out of habit (I like cleaning up anything I create). There is a myth out there that dropping a #temp table explicitly will prevent #temp table caching and reuse, but this is not true: See this blog post by @PaulWhite for a lot more details, and this one for even more.


How to get the oldest values over each id in a PostgreSQL table efficiently? How can PostgreSQL return a list of the oldest timestamp values over a table of sensor id measurements?

Let me explain the situation with a sample table:

CREATE TABLE sensor_data(
sensor_id INTEGER,
time TIMESTAMPTZ,
value NUMERIC,
PRIMARY KEY (sensor_id, time)
)

Populated table example:

+-----------+------------------+-------+
| sensor_id |       time       | value |
+-----------+------------------+-------+
|         1 | 2018-01-01 00:00 |     1 |
|         1 | 2018-01-01 01:00 |     2 |
|         3 | 2018-01-01 03:00 |     4 |
|         3 | 2018-01-01 04:00 |     3 |
|         4 | 2018-01-01 03:00 |     5 |
|         4 | 2018-01-01 04:00 |     6 |
+-----------+------------------+-------+

While using something like sensor_id (1,3) inside the query I want it to return something like this:

+-----------+------------------+-------+
| sensor_id |       time       | value |
+-----------+------------------+-------+
|         1 | 2018-01-01 01:00 |     2 |
|         3 | 2018-01-01 04:00 |     3 |
+-----------+------------------+-------+

How can I do that in a query using the PRIMARY KEY index for speeding it up?

There are many possible query styles, most will readily use your PK index on (sensor_id, time) as it fits the task. (Postgres can read indexes backwards practically as fast.) This should be near perfect:

SELECT s.sensor_id, sd.time, sd.value
FROM   unnest ('{1,3}'::int[]) s(sensor_id)
LEFT   JOIN LATERAL (
   SELECT *
   FROM   sensor_data sd
   WHERE  sd.sensor_id = s.sensor_id
   ORDER  BY time DESC
   LIMIT  1
   ) sd ON true;

db<>fiddle here

LEFT JOIN .. ON true keeps sensors without any data entries in the result - with NULL values in place of values.

Related:

Optimize GROUP BY query to retrieve latest row per user
What is the difference between LATERAL and a subquery in PostgreSQL?

Since you are on Postgres 11, a covering index might pay:

... PRIMARY KEY (sensor_id, time) INCLUDE (value)

But it makes the index bigger and writes to the table more expensive, and your names indicate a write-heavy table. And while you only query for few rows at a time, queries don't get much faster anyway. So probably best the way you have it. Related:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?


Can a trasaction be rolled back if a table is edited outside a transaction? I am learning how transactions work using this short script

CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;

START TRANSACTION;

INSERT
INTO    t_test
VALUES  (1);

SELECT  *
FROM    t_test;

 id
---
  1

SAVEPOINT tran2;

INSERT
INTO    t_test
VALUES  (2);

SELECT  *
FROM    t_test;

 id
---
  1
  2

ROLLBACK TO tran2;

SELECT  *
FROM    t_test;

 id
---
  1

ROLLBACK;

SELECT  *
FROM    t_test;

 id
---

Towards the end,ROLLBACK a savepoint is used.Does it mean when ROLLBACK is used it wipes out everything that was commited earlier on?.

Secondly,what if i edit the table outside of the transaction and the try ROLLBACK TO trans2 for instance,what happens?.

A SAVEPOINT is not a commit.  It doesn't "save" anything other than the "point" where you are, in the transaction, when you create it... allowing you to roll back to the savepoint, if you need to, without rolling back the entire transaction.

You can't alter a table in another connection while one or more transactions is holding locks against it -- the other connection will be forced to wait for the table metadata lock.  You also can't modify the rows, gaps, or indexes locked by one transaction from within another connection... so either way you try to "edit" the table from outside the transaction, the second requester will have to wait.

This is where deadlocks come in to play. In oversimplified terms... If I do something in a transaction, then you do something in a transaction, then I try to do something that requires me to wait on a lock held by your transaction, then you try to do something that requires you to wait for a lock held by my transaction, we are in a deadlock condition, where we would both literally wait forever, since neither of us can proceed without the lock held by the other.  InnoDB will detect this and force one or the other of us to forfeit our transaction by throwing a deadlock exception.  The "winner" will proceed unaffected, not knowing anything happened to the "loser."


Does anyone have example for setting up a job to reduce SQL Server max memory and recycle SQL server service? I have both SQL Server 2008 and SSAS running on the same machine (Don't ask me why, I have to live with it for now!). Since we run ETL and Cube generation in sequence, I wanted to allocate large portion of memory to SQL server right before ETL jobs start, and do the same for SSAS right before Cube generation starts.

Is there a way to automate this dynamic memory increase/decrease operations via a job?

Could someone with experience in this please share your thoughts/scripts?

Max/Min Server Memory setting in SQL 2008 is the buffer pool.  They do not include the memory that is allocated for worker threads, multipage allocator, DLLs and components that SQL loads in its address space, backup and restore operations, SSIS, SSAS, SSRS, and so on.  Set Max Server Memory low enough to leave sufficient memory available for those things not included in the buffer pool.  Windows will allocate the memory for you.

If it is your desire to have the Max Server Memory setting to be higher during the day, and adjust it lower for the ETL/SSAS jobs, then raise it back up again once they are completed, then you can script that with the following (#### is the MB being set, i.e. 1024):

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', ####;
GO
RECONFIGURE;
GO

Be warned that setting it to a lower number does not always result in the immediate return of memory to Windows.  If the memory is in use, SQL will release when it is no longer in use.


PostgreSQL JSONB: How to get an element from Array of Embedded Documents I am comparing MongoDB with PostgreSQL query abilities. I struggle a bit with finding simple equivalent for the following MongoDB queries.  What are the equivalent commands in PostgreSQL JSONB to MongoDB's:

Single Element Satisfies the Criteria
db.inventory.find( { memos: { $elemMatch: { memo: 'on time', by: 'shipping' } } } )

Combination of Elements Satisfies the Criteria
db.inventory.find( { 'memos.memo': 'on time', 'memos.by': 'shipping' } )

From: https://docs.mongodb.org/manual/tutorial/query-documents/#array-of-embedded-documents

SELECT ($${
   "_id": 100,
   "type": "food",
   "item": "xyz",
   "qty": 25,
   "price": 2.5,
   "ratings": [ 5, 8, 9 ],
   "memos": [ { "memo": "on time", "by": "shipping" }, { "memo": "approved", "by": "billing" } ]
}$$::jsonb)@>'{"memos":[{"by":"shipping"}]}';
 ?column?
----------
 t

Note that that only works with jsonb.

You'll probably find the documentation on json indexing useful too.


When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index? So I hear sometimes that I should not use a clustered index on a wide column, mainly because it is being referenced in all other indexes.

When all my other indexes on this table include all other columns - is there still this (extra) penalty?

(This table has no external references on the clustered index by other tables)

For reference my table looks a bit like this:

PartOfNaturalKey1    int
PartOfNaturalKey2    varchar(10)
PartOfNaturalKey3    varchar(20)
PartOfNaturalKey4    int
Value                varchar(10)
LastChanged          datetime2(7)
Invalid              bit

The Natural Key is unique and will (almost never) change.

If you have in every index all columns and you have a PK , and another 2 indexes , one on Value and one on LastChanged , you really multiplied the table by 4. This will make the table really slow for all operations.

Add an ID column , make it PK  and build indexes as you need based on the statements that run on the table.

Don't create an index on Invalid, it's a bit column , it's usually not useful .

Try to keep a rule that all indexes should be smaller in size than the table.


How to free temporary lob used in result set of SQL SELECT in Oracle A goal is to aggregate rows into very large strings.
I cannot use LISTAGG(XMLAGG,etc.) that support only up to 4000 bytes.
I looked into using PIVOT XML option but we have a strict requirement on using memory(XML is very bloated).

I tried using COLLECT along with to_string function to return CLOB value

CREATE TYPE vc4000_tt AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION to_string (
  coll           IN vc4000_tt,
  delimiter      IN VARCHAR2 DEFAULT ',',
  enclosed_by    IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB
IS
  lv_clob         CLOB;
  delimiter_len   CONSTANT PLS_INTEGER := length(delimiter);
  enclosed_by_len CONSTANT PLS_INTEGER := length(enclosed_by);
BEGIN

  -- Create CLOB
  dbms_lob.CREATETEMPORARY(
    lob_loc => lv_clob,
    cache   => true,
    dur     => DBMS_LOB.SESSION
  );

  -- Open it for writing
  DBMS_LOB.OPEN (
    lob_loc => lv_clob,
    open_mode => DBMS_LOB.LOB_READWRITE
  );

  -- Build CLOB from collections
  FOR i IN coll.FIRST..coll.LAST
  LOOP

    IF enclosed_by_len > 0
    THEN --write enclosed by
      DBMS_LOB.WRITEAPPEND (
        lob_loc => lv_clob,
        amount => enclosed_by_len,
        buffer => enclosed_by
      );
    END IF;

    DBMS_LOB.WRITEAPPEND (
      lob_loc => lv_clob,
      amount => length(coll(i)),
      buffer => coll(i)
    );

    IF enclosed_by_len > 0
    THEN --write enclosed by
      DBMS_LOB.WRITEAPPEND (
        lob_loc => lv_clob,
        amount => enclosed_by_len,
        buffer => enclosed_by
      );
    END IF;

    IF delimiter_len > 0
    THEN --write delimiter
      DBMS_LOB.WRITEAPPEND (
        lob_loc => lv_clob,
        amount => delimiter_len,
        buffer => delimiter
      );
    END IF;

  END LOOP;

  --Remove last delimiter
  IF delimiter_len > 0
  THEN
    DBMS_LOB.TRIM (
      lob_loc => lv_clob,
      newlen => DBMS_LOB.GETLENGTH( lv_clob ) - delimiter_len
    );
  END IF;

  DBMS_LOB.CLOSE( lob_loc => lv_clob );

  RETURN lv_clob;

EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.FREETEMPORARY(lv_clob);
    RAISE_APPLICATION_ERROR(-20900, sqlerrm||dbms_utility.format_error_backtrace );

END to_string;
/

select
  object_type,
  to_string(
    cast(collect(object_name) as vc4000_tt),
    ',',
    '"'
  )
from user_objects
group by object_type;

When Java app executes the query through a connection pool, that is a session stays forever, or when I execute it in SQL*Plus the number of temporary lobs goes up and up.

SELECT * FROM v$temporary_lobs;

I do not have any locators to use in dbms_lob.freetemporary call.
How can I free up these temporary lobs?

Is there an alternative way to return very large strings to a client app?

Before executing DBMS_LOB.CLOSE( lob_loc => lv_clob ); you need to execute dbms_lob.FREETEMPORARY(lv_clob);

According to the Oracle documentation it is required to free lob before closing.


'Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='' on query I have migrated the mysql 5.1 to 5.7 by using the mysqldump file. But i am facing the issue during  master slave replication as below.

Error 'Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='' on query. Default database: 'iiflcms'. Query: 'UPDATE site_user_master SET is_active = 'N', modification_date =  NAME_CONST('modificationDate',1615022896), passkey =  NAME_CONST('passKey',_latin1'9419' COLLATE 'latin1_swedish_ci') WHERE email_id =  NAME_CONST('userEmail',_latin1'Raj.fm.kangra@gmail.com' COLLATE 'latin1_swedish_ci')'

I have tried to check the default collection character set in database level as well as table colume level. it is showing the same as utf8. if it is the same then ther should not be any issue during replication.
but not sure why i am getting the above error on slave machine and beacuse of these error my replication is not stop.
i got workaround and set the collection and character set as latin1 and latin1_swedish_ci and my replication is started working. but problem is now on master character set and collection is utf8 and utf8_general_ci and in slave is latin1 and latin1_swedish_ci.
if i set these is there any problm in futues for few tables??

NAME_CONST is for internal use and has no possibility to convert text or use a collation
use the conventional way with CAST or CONVERT
UPDATE site_user_master
SET
    is_active = 'N',
    modification_date =  1615022896
    ,passkey =  CONVERT(_latin1'9419' USING latin1_swedish_ci)
WHERE
    email_id = CONVERT(_latin1'Raj.fm.kangra@gmail.com' USING latin1_swedish_ci)

Also it is not a good practrice to save passwords in plain text, there are a lot of function to hash the password https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html


count of rows in table day wise I need to get a count of rows in a table day wise.
The table looks like below:
Table ABC:
f_id|reg_date
1|2020-09-08
2|2020-09-12
3|2020-10-01
4|2020-09-07
5|2020-09-08
6|2020-09-09

Expected output if I am running query saying I want the count of rows till 2020-09-15 since a week before of 15th:
count|date
1|2020-09-07
3|2020-09-08
4|2020-09-09
4|2020-09-10
4|2020-09-11
5|2020-09-12
5|2020-09-13
5|2020-09-14

I am not sure how to get this above output.
The rouble with the date range and group by is just giving me the the count of rows for that date, not a total of the count till that date. For example group by date gives me: 1|2020-09-07, 2|2020-09-08 and so on.

One solution would be this:
SELECT DISTINCT
(SELECT COUNT(t2.f_id) FROM t t2 WHERE t2.reg_date <= dates.d) AS running_total
, dates.d
FROM t
RIGHT JOIN dates ON t.reg_date = dates.d
ORDER BY dates.d;

- see it working live in this sqlfiddle
As you can see, you need to provide the dates for which there is no data in your table in some way, if you really want to include them in your result. The easiest way is to have a table with some dates in it. Another way could be some views with fancy math.
Anyway, what you have to look out for in this solution is that you need to specify a column in the COUNT() function. With COUNT(*) it would count the NULL values, too.
There are other ways to solve this as well, especially with MySQL 8.0. Unfortunately I don't have time right now, but maybe I will find the time later. Or someone else adds another answer.
EDIT: to create for example 1000 dates starting from an arbitrary date (in this example the current date) on the fly, you could for example use this:
SELECT CURDATE() + INTERVAL c*100+b*10+a DAY AS thousand_days
FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3
ORDER BY thousand_days;


Should I backup or copy [an Access database file]? Access has the option of creating a backup of dbase, but is there any difference between doing this and 'saving database as'?

Thanks for your time and patience for the ignorant.

The "Back Up Database" and "Save Database As" options do the same thing. The only real difference is that "Back Up Database" will provide a default file name like

MyDatabase_2014-11-18.accdb

whereas for "Save Database As" the new name will default to

MyDatabase1.accdb

They both do what is essentially a "Compact and Repair" into a new database file. (The original database file is not compacted, only the resulting copy.)


When to create a new table if functionality is the same? I am having trouble making database schema/design decisions. The database I am building is relational.

Consider this example:

I have these tables: orders, products, companies.

I want to create links between two orders, two products, and two companies (to show that they are related). There is no additional data about the relationship which needs to be recorded, just the fact of the relationship itself (i.e. the relationship table just needs the two IDs and not any other fields - What I mean by this, is that there is no meta data or other fields for the relationship, I purely need to know that a relationship link exists, thus only need an object_one_id, and object_two_id).

I can create 1 table (relationships), or I can create 3 tables (order_relationships, product_relationships, company_relationships). If I just use 1 table, I will need a separate column to determine the relationship type (product, order, or company), and will need to look up the related table based on the object type. If I use 3 separate tables, then I can create a direct relationship between the foreign keys and primary keys across the tables (for example product_relationships would have a product_id, and related_product_id, which both link directly to products in the products table).

Here is role information:
For products, a product can have multiple other products to which the primary product is a comparable. This means that these other, related products, are considered the same physical makeup.

For companies, a company can have multiple other companies to which the primary company does business with. This means that these other, related companies, are considered vendors or customers.

For orders, an order can have another order to which the primary order correlates to. For example, if a company creates an invoice/sales order, then another order is created for the customer company as purchase order.

How can you decide which solution is better? What other questions would you ask to make the decision?

For the DBMS, I am looking to use either MySQL or MariaDB.

Quit thought (and MS SqlServer background).
Your order table will already have multiple tables, Header, Details.
You could 'link' your OrderDetails with the products directly...
Ok al basic design, but for Statistics and Query Oprimizers these direct links are gold, if yoy work with a relations table you cannot create a foreign key from that table to all tables as you don't know what parent value it will hold.
The parent could be a order, customer, product en the detail also order, customer, product, the kind of relation would be specified in a field.
Works fine for querying, that's not the issue, but on performance you would loose a lot i'm afraid.


MySQL mapping fields I have a table where I put tons of unique numbers from 1 to 9999999
In order.

It has 3 fields.
idnum,username,t

I set t to 1 when I use an idnum so that no one else can have that for a member id number.

I want to put username as the only place we put the username because we made it where people can change their username. And that was never considered when I programmed the website, because the owners told me they would not allow it, but they had overwhelming requests for it so they allowed it.

The problem is that in 9 years worth of programming, the first 6 I used username in most.

I can go add idnum to those tables and update it. My question is, CAN I update the tables to get the username in those tables to pull the username data from that table above where the idnum matches?

Kind of like mapping it, like Microsoft's "Access" does.

Is that possible?
If so where do I find that documentation on how to do it?

My goal is to do that so that if someone changes their use name changing it in that table would update it in all of them.

best regards,
-Richard

Yes, it's common to keep the username in one table, and then reference it by the id associated with that username. Then the idnum stays the same in all your tables, and you can change the username in one place.

You need to add idnum to each table where you store username.

ALTER TABLE favoritecolors ADD COLUMN idnum INT;

The idnum will be NULL by default after you create this new column.

Then update it to the idnum value matching the corresponding username:

UPDATE TABLE favoritecolors AS f
JOIN usernames AS u ON f.username = u.username
SET f.idnum = u.idnum;

If any of your tables contained a username that is not found in the usernames table, it won't update the idnum. So the idnum will still be NULL. Search to make sure this didn't happen:

SELECT username FROM favoritecolors WHERE idnum IS NULL;

This should return no rows.

After you map all your rows with a valid idnum, you can choose to drop the superfluous username from the dependent table, so the username is maintained in just one table (usernames).

ALTER TABLE favoritecolors DROP COLUMN username;

Make sure you first update all application code that queries username from any of your tables, so that it queries the idnum instead!


SSMS Registered Servers with ReadOnly Application Intent We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. This way they don't have to type in the alias name every time they need to access the ReadOnly replica.

Unfortunately there is no option within registered servers to add the ApplicationIntent Option unlike the regular object explorer.

I came across this article from Microsoft about changing the connection string in RegSrvr.xml.

https://connect.microsoft.com/SQLServer/feedback/details/786323/ssms-sql-server-management-studio-2012-missing-connection-properties-for-availability-groups

I tried their suggestion and it did not connect to the right replica node when connecting via the local server in the registered servers.

The ReadOnly option works fine from object explorer when using the options in the connect window > Additional Connection Parameters. But it does not save the changes made to the connection.

Does anyone know of any alternate solutions on saving the configurations with ReadOnly Intent property with SSMS? Thanks in advance for your help.

SQL Management Studio (versions prior to 2016)

Unfortunately, there are some caveats that make using Application Intent in SQL Management Studio somewhat painful:

To connect manually with ReadOnly intent, after bringing up the Connect
       to Server dialog from the Object Explorer, users must remember to:

Click Options >>.
Go to the Additional Connection Parameters tab.
Enter the additional parameter as ApplicationIntent=ReadOnly;
(Note: Users must not click the Options << button after entering
        the Additional Connection Parameters or the parameters will be
        lost.)
Click Connect.
Always launch query windows by right-clicking on the desired database in the Object Explorer view and choosing New Query to avoid running into the #3 caveat below.

The caveats that apply are as follows:

Although you can get SQL Management Studio to connect with Read Only Intent, it does not store the Additional Connection Parameters when a connection is added to Registered Servers.
Behavior when hand editing the locally registered servers in the RegSrvr.xml file to add the Application Intent is extremely inconsistent and will be overwritten any time a change is made through the GUI making this workaround unreliable.
The Always On database must be selected before the query window is opened; otherwise, the connection gets routed to the primary server. If you attempt to select the database using the query window's drop down after the query window has already been opened to a non-Always On database, you will get an error dialog. If you try to change the database to an Always On database with a USE statement after the query window has already been opened to a non-Always On database, the results look like this when you attempt to execute the SQL query:

      Msg 979, Level 14, State 1, Line 1
      The target database ('AlwaysOnDatabase') is in an availability group
      and currently does not allow read only connections. For more
      information about application intent, see SQL Server Books Online.

SQL Management Studio (versions 2016 or later)

SQL Server Management Studio 2016 or higher can connect with Read Only Application Intent (using the same 6 steps as prior versions) and it does store the Additional Connection Parameters. There are still some caveats:

The Object Explorer view will not enumerate any of the tables or
other objects in the AlwaysOn databases. Attempting to expand them
results in an access denied message.
You cannot have a Read Only and a non-Read Only connection to the
    same listener open at the same time.
Intellisense for object names in the database does not work. (Oddly
    enough, Objects get enumerated just fine in the Query Designer that
    you can launch using Design Query in Editor... from the
    right-click menu.)
Caveat 3 from the prior versions' caveats still applies.

Third-Party Products

LinqPad stores the whole connection string including Application Intent and the database when you save a connection and therefore might be a viable option for performing Read Only queries against Always On databases.


Can't resume synchronization after patch failed on secondary node of Availability Group I applied Service Pack 4 on secondary node of an Availability Group. It failed so I restored the whole VM for the secondary node to an earlier version, and now the data is not synchronizing.

I have active and passive two nodes. I tried to resume the data movement manually. But it failed. Can you suggest anything here?

When you restored the VM, its LSN was no longer in sync with the primary and thus it can't be resumed. The primary is too far ahead to resume.

You'll need to drop the secondary and add it to the Availability Group again in order to resume synchronizing. You can do this with an existing FULL backup plus the LOG chain.


Set Field To Have Default Value Of Another I am trying to create a trigger using the below syntax, however SSMS keeps barking at me with the below error.

  Msg 208, Level 16, State 4, Procedure test1, Line 1
  Invalid object name 'data123'.

And here is my syntax

CREATE TRIGGER test1
ON data123
AFTER UPDATE
AS
begin
  UPDATE data123
  SET saledate = saledate1
  FROM data123
  INNER JOIN INSERTED i ON data123.id = i.id
  AND i.saledate1 is not null;
end
go

Edit
For clarity - what I want to achieve is to set the field saledate = saledate1 where saledate1 is not null.

If there are better ways to achieve this then a trigger I am open for suggestions :)

EDIT #2
I also tried this syntax

ALTER TABLE data123 ADD CONSTRAINT cst123 DEFAULT saledate1 FOR saledate;

But I get an error of

  Msg 128, Level 15, State 1, Line 1
  The name 'saledate1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

saledate is a field that is used for multiple SSRS etc and when the database was created etc for data123 the field was improperly named to saledate1.  Not viable to try to rename it now as multiple elements are already tied to it.  So in order for data123 to be included in SSRS I need the field saledate to hold the value from saledate1

EDIT #3
Here is sample DDL to hopefully clarify what my problem is and my desired output.

Create Table test1
(
  uqID int IDENTITY(1,1) PRIMARY KEY,
  itemsold varchar(400),
  saledate1 date,
  saledate date
)
Insert Into test1 (itemsold, saledate1) Values
('tree', '03/01/2016')
,('flower', '02/11/2016')
,('chair', '02/25/2016')

Select * from test1

So if you run the above syntax this is your output

uqID    itemsold    saledate1   saledate
 1      tree        3/1/2016
 2      flower      2/11/2016
 3      chair       2/25/2016

My desired output (which is why I originally thought a trigger) is this

uqID    itemsold    saledate1   saledate
1       tree        3/1/2016    3/1/2016
2       flower      2/11/2016   2/11/2016
3       chair       2/25/2016   2/25/2016

For a one-off update to make saledate the same as saledate1:

UPDATE dbo.test1
SET saledate = saledate1;

You could also do this with a computed column. This would ensure saledate always matches saledate1 (even when the data changes):

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Add it back as a computed column
ALTER TABLE dbo.test1
ADD saledate AS saledate1;

A third option would be to drop saledate, then rename the saledate1 column:

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Rename saledate1 to saledate
EXECUTE sys.sp_rename
    @objname = N'dbo.test1.saledate1',
    @newname = N'saledate',
    @objtype = 'COLUMN';

This last approach is more complicated there are dependencies on the column. You would need to remove or update those before performing the rename.


Drop permission from a dbowner How can we remove the drop permission from a user or a role like dbowner? Want to restrict the dbowner from dropping the database.

Even if you meant Sql Server and just mis-spelled db_owner, the answer holds true for any database: use the principle of least priviledge. That is, if an user shouldn't be able to drop the database, do not assign such privileges in the first hand at all.

Messing with built-in security group and role settings is bad practice, unless you are very sure what you are up to. Create new roles with appropriate settings instead. This way, you or any other DBA doesn't have to double-check if default permissions are really on default settings.

That being said, you might be able to prevent drops with some creative triggers. As how to do that, depends on your RDBMS.


Instead of C:\SQLDBA\ServerName\Instance rather C:\SQLDBA\ServerName_Instance I am trying to create a folder based on the servername\instance to backup up to, i want the folder to create as servername_instance. Could you please point out where i am going wrong as it creates as servername\instance

IF OBJECT_ID('TempPSCreateFolder') IS NULL
BEGIN
CREATE TABLE [TempPSCreateFolder]
(QueryText nvarchar(max))
END

INSERT INTO [TempPSCreateFolder]

SELECT '$fso = new-object -ComObject scripting.filesystemobject

$fso.CreateFolder("C:\'+ STUFF((SELECT ',' + SUBSTRING(@@SERVERNAME, 1, CASE
WHEN CHARINDEX(',', @@SERVERNAME) > 0 THEN
CHARINDEX(',', @@ServerName)-1
                       ELSE LEN(@@ServerName)
                    END)

FOR XML PATH('')), 1, 1, '')+'")'

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

EXEC xp_cmdshell 'bcp master.dbo.TempPSCreateFolder out
"C:\SQLDBA\CreateFolder.ps1" -c -T -SServer\Instance'

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE

DROP TABLE TempPSCreateFolder

Your script seems unduly complicated. All you need to do to format the folder name according to the title of your question is replace the backslash in @@SERVERNAME with an underscore. No need for the table, script file, and XML PATH for this task. PowerShell also includes a New-Item command to avoid the need for the legacy COM FileSystemObject.

DECLARE @PowershellCommand varchar(8000) = 'powershell -Command "New-Item -Path \"C:\SQLDBA\'
    + REPLACE(@@SERVERNAME, '\', '_')
    + '\" -ItemType \"directory\""';
EXEC sp_configure 'xp_cmdshell', 1;  --assumes 'show advanced' config option is already enabled
RECONFIGURE;
EXEC xp_cmdshell @PowershellCommand;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

You could also avoid xp_cmdshell entirely and just run a stand-alone PowerShell script on the server:

New-Item -Path "C:\SQLDBA\ServerName_InstanceName" -ItemType "directory"


search misses indexed column I have a table like this

CREATE TABLE `agentpositions` (
    `idPosition` INT(11) NOT NULL AUTO_INCREMENT,
    `idAgent` INT(11) NULL DEFAULT NULL,
    `hardwareSerial` VARCHAR(128) NOT NULL,
    `LatitudePoint` DECIMAL(18,12) NOT NULL,
    `LongitudePoint` DECIMAL(18,12) NOT NULL,
    `PointDate` DATE NULL DEFAULT NULL,
    `TimeStamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ...
    PRIMARY KEY (`idPosition`),
    INDEX `idAgent` (`idAgent`),
    INDEX `idagent_pointdate` (`idAgent`, `PointDate`) -- next one is getting ignored and this one is getting used
    INDEX `pointdate_idagent` (`PointDate`, `idAgent`) -- this is getting ignored
)
ENGINE=InnoDB

we query this table with both (idAgent,TimeStamp) and because this table is going to get very big, we put index on both of them

but for sake of architecture (we think date-int is faster and more optimized than timestamp-int, is that event true?? ) we avoided putting index on timestamp, instead we added another field which stores only date of the timestamp, and added index on that, but I don't know why it ignores my index

my query looked like this:

SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
FROM `agentpositions` `t` IGNORE INDEX(`idAgent`, PRIMARY)
WHERE (
   t.idAgent=880 AND t.PointDate >= "2015-04-04"
   AND (t.TimeStamp >= "2015-04-04 00:00:00" and t.TimeStamp < "2015-04-04 23:59:59"))
   AND t.LatitudePoint != 0 and t.LongitudePoint != 0
ORDER BY t.TimeStamp

so after not identifying the index, I managed to change it to this which uses that index!

    SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
FROM agentpositions t IGNORE INDEX(idAgent, PRIMARY)
WHERE
t.idAgent=880 AND t.PointDate in ("2015-04-04","2015-04-05","2015-04-03" )
AND t.LatitudePoint != 0 and t.LongitudePoint != 0
order by t.TimeStamp

I want to be able to give time range like >='2014-01-01'

what is wrong with this index? how can I achive this?

UPDATE : I totally forgot to include my first index that did not get used, I updated the table

This is a nice a example to explain how the query optimizer works, because in my test server, with a couple or rows, your first query uses an index (look at the usage of EXPLAIN):

root@127.0.0.1(test)> EXPLAIN SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
    -> FROM `agentpositions` `t` IGNORE INDEX(`idAgent`, PRIMARY)
    -> WHERE (
    ->    t.idAgent=880 AND t.PointDate >= "2015-04-04"
    ->    AND (t.TimeStamp >= "2015-04-04 00:00:00" and t.TimeStamp < "2015-04-04 23:59:59"))
    ->    AND t.LatitudePoint != 0 and t.LongitudePoint != 0
    -> ORDER BY t.TimeStamp\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: idagent_pointdate
          key: idagent_pointdate
      key_len: 9
          ref: NULL
         rows: 1
     filtered: 7.11
        Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

While the second doesn't:

root@127.0.0.1(test)> EXPLAIN SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
    -> FROM agentpositions t IGNORE INDEX(idAgent, PRIMARY)
    -> WHERE
    -> t.idAgent=880 AND t.PointDate in ("2015-04-04","2015-04-05","2015-04-03" )
    -> AND t.LatitudePoint != 0 and t.LongitudePoint != 0
    -> order by t.TimeStamp\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: idagent_pointdate
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 38.40
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.01 sec)

The main reason why an index gets or not used is, first- if it can be used (this happens for both cases, as you can see on the possible_keys row, and second, if it considers using the key the best strategy to retrieving the rows (based on the cardinality of the columns).

I have shown this graph many times:

If the filter that can be used for an index is not very selective (for example, the range is too large), the optimizer will prefer to use a full table scan, as it is a faster strategy for reading a large number of rows. If you want to know the details for your particular query, and youa re using MySQL 5.6, you can use the OPTIMIZER_TRACE feature to understand why one plan was preferred over another.

If you believe a discarded plan is better, you can force the usage of an index by using FORCE INDEX(idagent_pointdate). Be careful because manually forcing the usage of an index is a dangerous practice, as if the data cardinality changes and the usage of the index is no longer the best plan, you will continue blindly using it.

Aside from your main question, there are many other issues regarding your query and indexes, that I will try to address here:

Your idAgent index is redundant- you can use idagent_pointdate for everything that you can do with that index, so it is useless, and will take away resources (memory, disk) but also slow down certain tasks like the query optimizer having to take it into account and writes (as an additional index has to be updated as well). Use tools like pt-duplicate-key-checker to search for redundant indexes on your database.
I do not get well what you mean with date-int and timestamp-int, but if you mean the practicality of having what basically is having redundant information, one storing second precision and another storing days: that may a useful physical optimization of the table IF you need to index days instad of timestamps. If you just need to index the timestamp using ranges, like t.TimeStamp >= "2015-04-04" and t.TimeStamp < "2015-04-05" (check the syntax, you are missing 1 seccond) this is again a waste of resources. There are cases like having an extra column is justified in order to index, for example, DAYOFWEEK(t.TimeStamp) or HOUR(t.TimeStamp) which cannot otherwise be indexed directly, as mysql lacks that functionality. I do not see its value here.
You will be unable to use 2 range accesses to two separate columns for the same index. Using IN + a list of values is a legit workaround for this limitation (which is intrinsic of B(+)TREE indexes). This may be your main issue here.
The use of the range cancels the possibility (in this particular case) of using the index for sorting- this cannot be workaround -either you use the index for filtering or for sorting.

Update: Because of B(+)TREE indexes, you can only use the first range for a multi-column index. I have another drawing of why here:

A composite index is just a BTREE ordered by the concatenated values:

The index with the reverse column order cannot be used in such an effective way because of the range at the start:

Technically, both indexes would be equally good as the should be of similar size, but for this particular query, the idagent_pointdate (idAgent, PointDate) is preferred because it has the range at the end, while for pointdate_idagent (PointDate, idAgent), only the first row can be used.


Unable to create a tablespace, unusual error I'm having difficulty creating tablespaces with Oracle 11gR2 on a fiber attached disk array. Permissions are correct and there is ample space available. I can create tablespaces on other mounts that were formatted nearly the same (difference in stride size that reflect different stripe sizes), the only major difference I can determine is that one filesystem is local disk and the other is external. I can create files by hand as the oracle user without any difficulty whatsoever. There is nothing of interest getting dumped to /var/log/messages. Googling the error hasn't produced much useful information either. I am somewhat new to Oracle. Any help would be greatly appreciated. Thanks

Here is output creating a tablespace on the local disk:

SQL> CREATE TABLESPACE ts_internal_test DATAFILE '/dbidx/idx0/ts_internal_test.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;

Tablespace created.

Here is output creating a tablespace on the external disk:

SQL> CREATE TABLESPACE ts_external_test DATAFILE '/dbdata/vg00/lv0000/ts_external_test.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE ts_external_test DATAFILE '/dbdata/vg00/lv0000/ts_external_test.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL
*
ERROR at line 1:
ORA-01119: error in creating database file
'/dbdata/vg00/lv0000/ts_external_test.dbf'
ORA-27044: unable to write the header block of file
Linux-x86_64 Error: 22: Invalid argument
Additional information: 3

Here are permissions, creating a file as the 'oracle' user, free space and mount options:

$ su - oracle
$ whoami
oracle
$ cd /dbdata/vg00
$ ls -l
total 20
drwxr-xr-x 3 oracle oinstall 4096 Apr 26 12:05 lv0000
drwxr-xr-x 3 oracle oinstall 4096 Apr 26 10:38 lv0001
drwxr-xr-x 3 oracle oinstall 4096 Apr 22 18:14 lv0002
drwxr-xr-x 3 oracle oinstall 4096 Apr 22 18:14 lv0003
drwxr-xr-x 3 oracle oinstall 4096 Apr 26 10:18 lv0004
$ cd lv0000
$ ls
lost+found
$ echo "test file" > some_file
$ ls -l
total 20
drwx------ 2 root   root     16384 Apr 26 11:42 lost+found
-rw-r--r-- 1 oracle oinstall    10 Apr 26 12:08 some_file
$ cat some_file
test file
$ df -h | grep lv0000
/dev/sdc1             2.0T   71M  2.0T   1% /dbdata/vg00/lv0000
$ mount | grep lv0000
/dev/sdc1 on /dbdata/vg00/lv0000 type ext2 (rw,noatime)

NOTE: we are using ext2 due to limitations that we have to work around within our environment. The volume is currently mounted as (noatime,async) based off a recommendation I found in regards to Oracle and ext2/3. I've tried mounting with defaults but it didn't change anything.

-Daniel

Tracked it down. I set FILESYSTEMIO_OPTIONS=SETALL the other day. It appears that direct I/O works for the local RAID array but not for the external. When I set it back to NONE, I was able to create tablespaces on the external device.

A follow up: Does anyone know if you can enable direct I/O on a per device basis without using ASM or RAW?


How to check foreign keys related to a table How to see foreign keys related to a table in MySql?

Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this:

Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails

  How can I drop foreign keys related to the table leaving others.

Firstly, find out your FOREIGN KEY constraint name in this way:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- the one you want!
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'My_Table';

You can also add (to the WHERE clause) if you have more than one table called My_Table in different schemas.

AND TABLE_SCHEMA = 'My_Database';

And then you can remove the named constraint in the following way:

ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint;

References: 1 & 2.


Deadlock error isn't returning the deadlock SQL Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I am getting this error randomly when one of my websites gets busy. I know roughly which sets of tables it is happening on but in my experience with other programs I normally get the SQL returned where the deadlock is happening. Is there a flag I should turn on to allow this to happen?

I will try and debug the deadlock itself as a seperate issue as this is my main question for now.

I am using SQL Server 2008 Standard Edition.

The data you need is recorded in the default extended events trace.

DECLARE @xml XML

SELECT @xml = target_data
FROM   sys.dm_xe_session_targets
       JOIN sys.dm_xe_sessions
         ON event_session_address = address
WHERE  name = 'system_health'
       AND target_name = 'ring_buffer'

SELECT
             XEventData.XEvent.query('(data/value/deadlock)[1]')  AS DeadlockGraph,
             CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph,
              XEventData.XEvent.value('(./@timestamp)[1]', 'DATETIME2') AS [DateTime]
FROM   (SELECT @xml AS TargetData) AS Data
       CROSS APPLY
       TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
ORDER BY [DateTime] DESC

Though it won't be there any more if you have restarted the service -e.g. to apply a trace flag or if the buffer has cycled in the meantime.

You can set up your own extended events trace that stores the deadlock graph to a file target for persistent non volatile storage. Example Code here. I personally find the deadlock graph XML more friendly than the trace flag output.

Edit

@MartinC points out in the comments that on instances of SQL Server that don't have all the updates there might be a problem with it generating invalid XML. The fix for that is to do some search and replace and use CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph in the SELECT list as described here.
Wayne Sheffield has posted a useful script to shred the deadlock graph XML into tabular format here.


How to create a conditional index in MySQL? How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature.

Example: I want to create an index for NAME column just for rows with STATUS = 'ACTIVE'

This functionality would be called a filtered index in SQL Server and a partial index in Postgres.

MySQL currently doesn't support conditional indexes.

To achive what you are asking (not that you should do it ;) ) you can start creating an auxiliary table:

CREATE TABLE  `my_schema`.`auxiliary_table` (
   `id` int unsigned NOT NULL,
   `name` varchar(250), /* specify the same way as in your main table */
   PRIMARY KEY (`id`),
   KEY `name` (`name`)
);

Then you add three triggers in the main table:

delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   END IF;
END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   ELSE
      DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
   DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
END;//

delimiter ;

  We need delimiter // because we want to use ; inside the triggers.

That way, the auxiliary table will contain exactly the IDs corresponding to the main table rows that contain the string "ACTIVE", being updated by the triggers.

To use that on a select, you can use the usual join:

SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table
   ON auxiliary_table.id = main_table.id
   ORDER BY auxiliary_table.name;

If the main table already contains data, or in case you make some external operation that changes data in an unusual way (E.G.: outside MySQL), you can fix the auxiliary table with this:

INSERT INTO auxiliary_table SET
   id = main_table.id,
   name = main_table.name,
   WHERE main_table.status="ACTIVE";

About the performance, probably you'll have slower inserts, updates and deletes. This can make some sense only if you really deal with few cases where the condition desired is positive. Even that way, probably only testing you can see if the space saved really justifies this aproach (and if you are really saving any space at all).