Maintain index on SQL Server table My problem is how do I maintain an index on table, weekly we are deleting and inserting bulk amount of data from the table.Because drop and create index taking too long time..

You could set up a job to rebuild or reorganize them weekly/daily.

I recommend checking out http://sqlfool.com/2011/06/index-defrag-script-v4-1/

This index rebuilder is highly customizable.


BCP stops after the last "1000 rows sent to SQL Server. Total sent: ..." I ran SQL Server 2005's bulk copy program BCP like this:

bcp mydb.dbo.mytbl in myfile.blk -c -S mysvr -U mylogin -P mypass

And it ran and produced output like so:

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
...
1000 rows sent to SQL Server. Total sent: 55000

But then it stopped. The cursor did not return to prompt, I haven't received the "... rows copied." message.

I tried querying the target table and I can already see the rows that I wanted to import.

Shall I terminate the console? Will it rollback?

After 3 hours, I finally got my prompt back:

55837 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 10582641 Average : (5.28 rows per sec.)

Although I still don't know why it didn't return immediately since the rows were already inserted. My best guess was it did index rebuilding.

I will try terminating the console the next time it hangs up like that.


Query does not use index when selecting all records This is my first time handling a large database, and I was wondering why the two queries below are different in terms of execution plan.

I have setup an index on a specific table:

CREATE NONCLUSTERED INDEX [IX_UBHSD_xploc_trandt2] ON [dbo].[UBHSD]
(
    [xploc] ASC, [trandt2] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
        DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
        ON [PRIMARY]
GO

When I ran this query, it uses the index.

SELECT COUNT(*) as cnt
FROM [dbo].[UBHSD]
WHERE [xploc] != '' AND [trandt2] >= CAST('2012-01-01' AS DATETIME)

Here's a larger one.

But when I ran a query without using aggregate:

SELECT *
FROM [dbo].[UBHSD]
WHERE [xploc] != '' AND [trandt2] >= CAST('2008-01-01' AS DATETIME)

Here's a larger one.

I was expecting that both queries would use an index. But the second query performs a full table scan.

Why is this happening? Did I miss something?

You've used SELECT * which, presumably, has more columns than xploc and trandt2. So SQL Server can easily derive the count by traversing the index pages. It can't get the other columns that aren't in the index from there, so it has to go to the table.

Depending on how many columns are in the table (and a host of other factors), SQL Server can usually make one of two choices in a case like this:

Perform a seek (which looks like a range scan in your case), and then perform key/RID lookups to retrieve the other columns not covered by the index.
Suck it up and perform a scan.

In your case there are probably enough rows satisfied by the WHERE clause that it is actually more efficient to perform the scan.

Some ways to make this more efficient:

Stop using SELECT * - just list the columns you need.
Once you have whittled down to the actual columns you need, change the index to INCLUDE the other columns in the actual SELECT list you need.
You could consider making an index filtered by adding a WHERE clause, depending on whether this exact query is one that is run frequently (and if there is a lot of data pre-2012).

As an aside, why is there no clustered index on this table?


SQL Server: efficient query with (double) linked servers I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version).
I have no control on server C, and I cannot contact its DBA.  I am not even sure that server C is Microsoft SQL Server, it might be Oracle.

On server B, I can contact the DBA and ask for a view on server C, example:

Create view [dbo].[View_Stuff] as
select * from
openquery (SERVER_C, 'SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x')

I would like to do things like:

SELECT * FROM View_Stuff WHERE mykey=27

This view gets a lot of data from server C, because the openquery gets all the content of the table, which is not acceptable. On the other hand, I know that this would extract only one row, and would be very efficient on server C:

SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27

I considered using indexed views, but they cannot work with linked servers.
Mirroring the data is possible, but I really want real time information.

What can I ask to define on server B to be able to get data efficiently and in real time from a query on server A?

Since you know exactly the T-SQL you'd like to run on server 'B', why not just execute that query remotely, at server 'B', like this:

DECLARE @cmd nvarchar(max);
SET @cmd = N'SELECT t.c1
    , t.c2
    , t.c3
FROM OPENQUERY (SERVER_C, N''SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27;'') t;
';
EXEC [SERVER_B].tempdb.sys.sp_executesql @cmd;

That will cause server 'B' to query server 'C' with all the parameters you want, and should be about as quick as you can get.


Return Output from SQL Function using command line SQLCMD I have a SQL Function that returns an Integer as the Output. I need to execute it via sqlcmd and I only want to get it's integer output.

However right now I'm getting back this type of output:

------------
      25

<1 rows affected>

How can I get my output to be just 25 with no other text.

Here's the Call I'm using:

sqlcmd -S MyServer\Instance -d SQLAdmin -Q "SELECT dbo.UDF_INT_Get_Status ('INT_02')"

You are looking for the headers option of sqlcmd. Adding -h -1 will suppress header information.

sqlcmd -S MyServer\Instance -d SQLAdmin -Q "SELECT dbo.UDF_INT_Get_Status ('INT_02')" -h -1


Are CNAME aliases for connection strings a good idea? To be very flexible and move database to different servers more easily, we came up with the idea of using an alias per database. So by simply changing a DNS entry, we can route all queries to a different database server without changing the connection string or re-deployment of the respective client application.

To clarify:

Per database we would have an alias. So if the database is moved to a different server, only its DNS alias has to be changed.

For example:

db1-alias.mydomain.com points to db1 on some random server.
db2-alias.mydomain.com points do db2 on some other random server.

So lets say db1 was moved from server1 to server2, there is no need to change the connection string. Only the DNS alias entry has to be changed, forwarding to server2 instead of server1.

Are there any known drawbacks to this?

One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections.  This could be a significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application.

If you are using a separate alias for each application, you would need to generate and install a new certificate each time you add a new application to the server.  The same might apply if you want to make sure there are no unused/invalid SANs in the certificate, in that you would need a new certificate each time you removed an application.


Wildcard for table permissions I want to grant a user permission to select,insert,update,delete on a few tables in the prod database. However, I find it time consuming to write each grant statement out per table. Is it possible to use a wildcard?

There are 300 tables, the user only needs access to 18 of them. The tables the user needs access to are prefixed 'vs_'.

Can I do a grant select,insert,update,delete on prod.vs_\* ?  I know prod.\* is possible but wasn't sure on the prefix of a table.

No, the wildcard for table names can only be *, and does not permit other characters or patterns.

You could generate the 18 GRANT statements you need:

SELECT CONCAT('GRANT SELECT,INSERT,UPDATE,DELETE ON prod.`', TABLE_NAME, '` TO ...;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'prod' AND TABLE_NAME LIKE 'vs\_%';

Capture the output of that query, and then run it as a series of statements.


Always On Setup - OBJECTSTORE_SERVICE_BROKER Memory clerk eating all the memory We have an Always On setup where in we have two secondary replicas to our primary replica. One of the secondaries is placed in the same datacenter as the primary and the other one is placed at a distant DR site.
Currently we have both the secondaries configured with Asynchronous availability mode.

We have a peculiar issue, wherein a specific memory clerk on the primary replica gradually eats up all the memory allocated to SQL Server in a few days time and we are forced to restart the SQL Server services on the primary to get the memory allocation back to the buffer pool.

We are running SQL Server 2016 SP1-CU8 Enterprise Edition on all the 3 SQL Server instances involved. We approached MS with the issue and they suggested to upgrade to SP2 CU1 and then come back. We tried to upgrade to SP2 CU1 in our test environment and somehow the installation itself failed.

I am attaching a snapshot of the memory allocation across different memory clerks to indicate the particular one that eats up all of it.
Haven't been able to find much on this issue. Any help/support/direction would be appreciated.

Query to find Top clerks consuming memory.

select sum(pages_kb+awe_allocated_kb)/1024 as Memory_in_MB,
 type from sys.dm_os_memory_clerks
 group by type
 order by sum(pages_kb+awe_allocated_kb)/1024 desc

Output:

153824     MEMORYCLERK_SQLBUFFERPOOL
74591      OBJECTSTORE_SERVICE_BROKER
7971       OBJECTSTORE_SNI_PACKET
526        MEMORYCLERK_SOSMEMMANAGER
168        MEMORYCLERK_SQLSERVICEBROKERTRANSPORT
115        MEMORYCLERK_SOSNODE
91         MEMORYCLERK_SQLSTORENG
79         USERSTORE_SCHEMAMGR
65         OBJECTSTORE_LOCK_MANAGER
42         MEMORYCLERK_SQLTRACE

Additional Info:

All the 3 machines have a physical memory of 262033 MB. They all run 16 logical processors each.

Server A (runs the primary replica). Max Server Memory: 240000 MB.
Server B (runs the secondary replica at the same datacenter). Max Server Memory: 240000 MB
Server C (runs the secondary replica at the distant datacenter). Max Server Memory: 250880 MB.

There are 2 AGs.

The first one has 7 databases.
The second one has 11 databases.

You mentioned that you're not explicitly using service broker anymore, but Availability Groups (AGs) use service broker behind the scenes.  This system is called the Usage Control Service (UCS) and is used for queuing and sending groups of log blocks from the primary to the secondaries.

I was able to find at least one bugfix for this system causing memory issues:

FIX: Service Broker UCS task leaks memory in SQL Server 2014 or 2016

  Assume that you have AlwaysOn Availability Groups configured in SQL Server 2014 or 2016. If an application uses the TABLOCK hint, the Service Broker Usage Control Service (UCS) task leaks memory. In this situation, you can see that the number of tasks in the sys.dm_os_tasks function continuously grows. This causes AlwaysOn synchronization to become slow.

Note that this bug was fixed in SQL Server 2016 (RTM) CU2, so unless this is a regression, this isn't the specific bug you're facing.

All that to say, this could be a legitimate bug in the product that's been fixed in a newer version.  If you're having trouble updating to SP2, consider trying to at least updating to the latest CU for SP1 to see if the problem has been resolved there.


Create Table not working Forgive me if the question is very simplistic; I am a bit of a novice when it comes to SQL. The following CREATE TABLE syntax was generated by MySQL workbench from my model; It gives me errno: 150. Anyone who can tell me what I am doing wrong gets a cookie (and my gratitude!).

CREATE TABLE IF NOT EXISTS `mealPlannerApp`.`ingredients` (
`ingredientId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`stub` VARCHAR(40) NOT NULL,
`name` VARCHAR(40) NULL,
`photo` INT NULL,
`description` TEXT NULL,
`servingSizeInGrams` INT UNSIGNED NULL,
`cost` INT UNSIGNED NULL,
`calories` INT UNSIGNED NULL,
`protein` INT UNSIGNED NOT NULL,
`carboydrates` INT UNSIGNED NOT NULL,
`fat` INT UNSIGNED NOT NULL,
`saturatedFat` INT UNSIGNED NULL,
`transFat` INT UNSIGNED NULL COMMENT ,
`cholesterol` INT UNSIGNED NULL,
`sodium` INT UNSIGNED NULL,
`dietaryFiber` INT UNSIGNED NULL,
`sugars` INT UNSIGNED NULL,
PRIMARY KEY (`ingredientId`),
UNIQUE INDEX `id_UNIQUE` (`ingredientId` ASC),
UNIQUE INDEX `stub_UNIQUE` (`stub` ASC),
INDEX `photo` (`photo` ASC),
INDEX `name` (`name` ASC),
CONSTRAINT `photoId`
FOREIGN KEY (`photo`)
REFERENCES `mealPlannerApp`.`photos` (`photoId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

Error 150 is a FOREIGN KEY related error so I bet that you didn't create the table mealPlannerApp.photos before mealPlannerApp.ingredients (which is mandatory).

Otherwise, you have some bad practices in your query:

PK on ingredientId and unique index id_UNIQUE are duplicates (useless and will generate extra-load)
The ASC option in your indexes definition is understand by MySQL but not implemented (useless), maybe it's Workbench which has generated this.


Can somebody help me understanding the difference between view,materialized view and snapshot? Can somebody help me understanding the difference between view,materialized view and snapshot?

snapshot is an old and deprecated term for a materialized view.  They're exactly the same thing, snapshot is just an older term.

A view is a stored query.  It consumes (effectively) no space, just the space required to store the text of the query in the data dictionary.  When you query the view, the stored query is executed.  The results, therefore, will always be up to date.

A materialized view is a both a stored query and a segment.  The stored query is executed and the results are materialized into the segment.  That means that you can do things like index the materialized view.  It also means that if you query the materialized view, you may see stale data (depending on how you have configured the materialized view to refresh itself).  Depending on a variety of factors (whether query rewrite is enabled, whether stale data is tolerated, whether the materialized view is up to date, whether dimensions exist), materialized views can be used by Oracle automatically to speed up queries that do not directly reference the materialized view.  This is particularly useful when you're building a data warehouse and want to be able to pre-aggregate data for various reports.


Always On reaction in specific scenario I need to upgrade my Sharepoint2013 Farm, and the SQL2012 servers are in AlwaysOn mode.

I was wondering if I could use Always On in the following catastrophic scenario:

Take a VM snapshot of Sharepoint Web Front End servers;
Shut down SQL#2;
Upgrade Sharepoint Farm (we are 1 SP and 27 CUs short);
Catastrophic failure during upgrade or upgrade fails somehow;
Shut down SQL#1;
Shut down Sharepoint farm;
Power on SQL#2 (so AO listener is now pointed here);
Restore Sharepoint VMs;

Questions:

Am I all back to normal as if upgrade never happened at this point? Would this really work?
Now, if I power on SQL#1 at this point, will #2 resume Always On on #1 properly, or will I get some sort of desynchronised alerts? Or, in the worst that could happen, will SQl#1 start to override SQl#2?

Thanks

The steps you have should almost work. Assuming that there are database changes in the upgrade (I'm assuming there are because you want to keep SQL #2 offline so it doesn't upgrade) then SQL #2 won't be able to automatically or gracefully become the primary, as there is no quorum it can use. You'll need to force a failover with data loss.

For your questions:

I can't see why it wouldn't work. SQL #2 doesn't know about the changes on SQL #1, the sharepoint farm doesn't know about the changes, everything should resume as normal
If you power on SQL #1, it will realise that it's not the primary, but it will also be impossible to make it synchronise again. You'll have to drop the database from #1, break the AlwaysOn Availability Group and reseed it again. Because #1 isn't the primary I can't see how it will write data over #2.


Optimisation and Performance Mysql , Zend Framework 2 and Doctrine 2? i'm facing some problem with performance in an application using zend framework2 and doctrine 2. i would like to optimis it but i don't know from where to start , i read some many questions/ answers about performance and optimisation and i got using cache and indexation But my problem is where exactly apply the indexation is on doctrine or on mysql ? the cache where on doctrine or zend cache or the both ?? i can't figure out from where to start espacially that there a high traffic on the application so also the cache might not be the right solution !!!
plz any help or idea would be appreciated.
Regards.
Thanks

Start with the database and work your way up. Check out the queries you run using the command line before sending them through a framework. You can activate the general log to check every query that's sent to the database if you want to check exactly what, exactly, is arriving at your server after having used the framework. Frameworks can be good (relieving you of tedious chores), but they can be the source of (unpleasant) surprises.

You don't mention what version of MySQL you are using, but if 5.5 and above, you can make use of the Performance Schema (P_S). The P_S is MySQL's (embryonic) implementation of an Oracle Wait Interface style system for instrumenting the database. It tells you where time is being spent and this allows you to concentrate on the major problems and address them with hardware (if necessary - i.e. more/better RAID if disk-bound) or query tuning (if one/some queries are taking a lot of time). You should try and use at least 5.6 for this (is there any reason not to?).

If you're having problems with specific queries, you can post back here (new question) with DDL (SHOW CREATE TABLE my_table) and sample data (INSERT INTO my_table VALUES(...)). You can use SQLFiddle and even provide a download (you can hash sensitive data).

Sorry not to be able to provide anything more specific, but your question is quite broad and general - as I say, when you run into specific issues, post back here and we might be able to help.

[EDIT in response to OP's comment]

I'm assuming that this doctrine framework generates queries, but the answer is no, you won't have to generate indexes through the framework. My experience (not huge) with frameworks is that they will generate queries which give you the data you want.

The indexes are normally left up to the developer because many/most/nearly all of these frameworks are designed to work with multiple RDBMSs (this is not necessarily a good thing!), so they do the bare minimum (some would say the lowest common denominator) of SELECT FROM, INSERT INTO and UPDATE which is fairly standard SQL. However, the syntax for creating indexes varies from server to server (as for many other things), so the frameworks in general don't handle this well.

Ensure that your framework is giving you the correct data, then see if adding indexes can help speed up your particular application, because no framework can possibly anticipate all use cases (table sizes, RAM, my.cnf... the list goes on).

All I can say is that now if you're having a problem with a particular query, post a new question here giving the details I mentioned. Check the performance of the framework generated query and then check with your own choice of indexes. Don't forget that a system is made up of many queries and that modifying one may adversely affect others. Indexes can provide enormous benefits, but they do have downsides such as slowing down UPDATEs and INSERTs. Ideally, after the first stages above, you should have some sort of system for replaying your new queries with a simulated system load - see here. Good luck with your system.


How do I get % progress for "type db.sql | mysql" I am doing:

type db.sql | mysql

and the console output is nothing. I see the ibdata1 file growing, but I don't know how many GB it will grow to.

I would like to see a % progress indicator of some kind. Any ideas?

Have I got a crazy query for you !!!

This will report on the size of every InnoDB table.

SELECT CONCAT(db,'.',tb) table_name,CONCAT(ROUND(
tbsz/POWER(1024,IF(pw<0,0,IF(pw>4,4,pw))),3),' ',
SUBSTR(' KMGT',IF(pw<0,0,IF(pw>4,4,pw))+1,1)) table_size
FROM (SELECT data_length+index_length tbsz,table_schema db,table_name tb
FROM information_schema.tables WHERE engine='InnoDB') A,
(SELECT 3 pw) B;

(SELECT 0 pw) give Bytes
(SELECT 1 pw) give KB
(SELECT 2 pw) give MB
(SELECT 3 pw) give GB
(SELECT 4 pw) give TB

Just run this periodically during the loading of the SQL script. If the SQL script table locks the table during the load, then you will not see numbers until the table is fully loaded.

Give it a Try !!!


Conversion failed when converting date and/or time from character string while inserting datetime sql server I'm working with a project in SSRS (SQL Server Reporting Services) and project is about employees of company,where i should find out age of 200 employees from their Social Security Number (CPR).

In Database i have a column called SSN and in this column , i have Social Security Numbers for all employees and all numbers look like this with out any ( - or /) example 1111111111 (DDMMYYXXXX) and Datatype is nvarchar(30).

When i executed my query ,i get error 'Conversion failed when converting date and/or time from character string'. Can someone please point me in the right direction. Thanks.

My query look like this :

SELECT Users.Id, Users.FirstName + ' ' + Users.LastName AS Medarbajder,
Users.SSN AS CPRNR,

DATEDIFF(YEAR,CONVERT(date,STUFF(STUFF(LEFT(Users.SSN, 6), 3, 0, '.'), 6, 0, '.'), 4), GETDATE()) As Age,
convert(varchar(10), Paychecks.WorkStartDate, 105) AS StartDato ,

Paychecks.DepartmentName AS Afdelinger

FROM dbo.Paychecks, dbo.Users
WHERE Users.CustomerId=214 AND Users.Id=Paychecks.UserId order by Users.FirstName;

Bad news: social security number isn't in the format of DDMMYYXXXX. (My own SSN starts with a number above 3, for example.)

Take out the DATEDIFF and CONVERT functions, and just look at the source data. You'll likely see the offending rows just by eyeballing it:

SELECT Users.Id, Users.FirstName + ' ' + Users.LastName AS Medarbajder,
Users.SSN AS CPRNR,
SupposedlyDate AS STUFF(STUFF(LEFT(Users.SSN, 6), 3, 0, '.'), 6, 0, '.'),
FROM dbo.Paychecks, dbo.Users
WHERE Users.CustomerId=214 AND Users.Id=Paychecks.UserId
order by 4;

I don't know where you got that formula from, but it doesn't work. (It certainly doesn't produce a valid date for my own SSN.) There are ways to guess approximate dates from social security numbers, but not produce valid birth dates.


imp paused mid import OK, I'm new to Oracle administration but I'm trying to import a previous dump (no pun intended) which was split into two files.

The import is happening from an Oracle 9i to 11gR2 on Windows

I used the following commands:

imp userid=olduser/oldpassword indexes=n log=import.log
file(Export_DB_776full_20130829_020000_01,Export_DB_776full_20130829_0200
00_02) fromuser=olduser touser=newadminuser commit=y feedback=100

Now this has progressed fairly well and has imported data from what seems to be the first file, peppering the console with a dot every 100 rows imported.  Then when it reaches the table where the export was split (into the second dmp file) its just paused.  I thought it was simply collecting its thoughts and left it overnight but with no progress

Trying to connect to Oracle from SQLPLUS on the command line, even as SYS or SYSDBA is not possible, and returns with a

"ORA-00257: archiver error. Connect internal only, until freed."

Googling has yielded no direct answers to this but what I did find at least yielded some access to the database to perform some alteration function. Some of the other posts I saw mentioned that the log files are filling up, but there is well in excess of 150 GB free on both drives (the tablespace is split across two drives)

A few things:

What is going on? - because the error message seems a bit cryptic to me -
How can I connect to the database?
How can I fix this?
How do I not fall into this trap again?

Thanks

your database is running in archivelog mode. An initial backup + archives allow you to recover your database to any point in time between the backup and the last available archivelog file.
If this is a big import you are most likely going to make a backup afterwards. If you have no need to recover the database to a state between now and the previous backup, you could use rman to delete the archived log files, freeing space so the archiver can continue.
An other option is to increase the db_recovery_file_dest_size parameter that could be in place since you claim to have plenty of space available.

sqlplus / as sysdba
show parameter db_recovery_file_dest_size
alter system set db_recovery_file_dest_size = {A_BIGGER_SIZE}

or

rman target=/
delete archivelog all;

don't forget to make a backup, deleting the archives makes recovery impossible.


Duplicate all stored procedures with a new name I want to take all stored procedures and create them under a new name. My problem is with procedures that are called from other procedures .

To rename a procedure is no problem for me. But to change/duplicate the procedures that still reference them by their old names is a problem.

For example:

CREATE PROCEDURE dbo.getdata
AS
BEGIN
   EXECUTE dbo.up_updatetable ;
END;

How about such a little script, to generate new scripts for all procedures, replacing all old names with new names (definition and calls):

SELECT
    O.Name as ProcName
    ,REPLACE(REPLACE(REPLACE(
        M.Definition,
            'old_proc_name_1', 'new_proc_name_1'),
            'old_proc_name_2', 'new_proc_name_2'),
            'old_proc_name_3', 'new_proc_name_3')
        -- etc.
as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.objects as O
ON M.object_id = O.object_id
WHERE O.type = 'P'
and O.name not like 'sp%' -- exclude special system procs

Then you can execute all the scripts, either by hand, or using a cursor (more prefered for hundreds of procs).

But begin with checking carefully at least a couple of scripts before executing them.


Improving sql query execution time for 3 billion rows table I have a 2+ billion row mysql table. I am also writing 1 million rows each day. The table contains some large columns like varchar(255) (to hold long urls)
In order to perform analytics on this table I created 5 specific indices which really speed up the execution time. (From 25+ minutes to 2 minutes for some queries).
The problem still persists that 2 minutes is a lot of time for just one query. I would like to run multiple queries for analytics and reporting.
Also, this table is increasing rapidly on a daily basis and I am pretty sure the indexes are as optimised as they can be.
Is this the point where clustering would solve my issues? i.e. is it unusual that a table this size still runs on a single sql node?
or is it still possible to run queries in such large table in milliseconds?
An example query of mine is:
SELECT name, url, SUM(visits), AVG(price), AVG(loc) FROM mytable
    WHERE sname IN ('white') AND usage IN ('three') AND date BETWEEN '2001-01-01' AND '2003-03-10'
    GROUP BY name, url ORDER BY SUM(visits);

I am new to clustering and HPC in general any advice on what I should do here is appreciated.

There is a lot of information not included in your question, which makes providing complete answers rather difficult. However, using just what you've shared:
Is this the point where clustering would solve my issues?
Not likely. Clustering offers a lot of advantages, but it does not seem to be the correct solution for what you're trying to do. With a million rows being added daily, your primary system needs to be optimized for writes. When talking about reporting, you could probably do with a system that is optimized for reads.
Is it unusual that a table this size still runs on a single SQL node?
The level of unusualness here depends greatly on the needs (and expectations) of the business. I would hope that there is a hot spare or replicated instance somewhere that is ready to jump in should the main server suffer a failure. At 11.5 record insertions per second, there's not much room for downtime.
Is it still possible to run queries in such large table in milliseconds?
Given enough hardware, I don't see why not. However, very rarely are people given access to the full computing power of an entire data centre.

Generally when I have had to work with situations like this, I've tried to keep things simple and ask specific questions regarding the types of reports that people are trying to collect from the system. If there are common patterns, then flattened historical tables have saved a great deal of time for everyone. Why query the same data from 2003 a thousand times a week when you can summarize it in a manner that makes long-view reports faster and just as accurate, after all?
However, one of the main ways I've solved this sort of issue — usually for universities that are collecting millions of records daily from seismometers and weather stations spread throughout the country and across the Pacific — is to "cheat" and have a replicated instance that updates its source tables only once or twice a day. This allows for a system that can be optimized for reads with a myriad of indexes, leaving the main server(s) optimized for writes with fewer indexes (if any at all).
For common reports, patterns in the data are found and put into summary tables on an hourly/nightly schedule, allowing for common reports to be quick. Ad hoc or more specific queries can also be run against the replicated instance without worrying about affecting performance on the main system(s). So long as the reports being generated do not need to be "real-time", this method generally works and can be done on a reasonable budget, which management types tend to appreciate.
Mind you, do not take this answer as anything beyond something to think about. As mentioned at the beginning, there is a lot of information that was not included in the original question, such as the target audience for the reports, what other tasks the database(s) are being used for, how often historical data is queried vs. current data, and the like. This is just an option based on past experiences when I have been asked to solve a similar-sounding problem.


Very Simple MySQL Inner Join Extremely Slow I have setup our first MySQL instance and am testing some simple queries. I am attempting to join two tables based on a 9 character string. The field is indexed on both tables and yet the query is taking longer than 10 minutes. When I run the same query on our MSSQL server it runs in under 1 second. I must be doing something wrong.

table_a
1,000,000 rows

table_b
5,724,842 rows

query:

select count(*)
from table_a
inner join table_b on table_a.field = table_b.field

When I run EXPLAIN I see:

select_type | table | type | possible_keys | key | key_len | ref | rows| extra
SIMPLE | table_a | index | ix_name | ix_name | 30 | NULL | 962446 | Using index
SIMPLE | table_b | ref | ix_name | ix_name | 30 | table_a.field | 1 | Using where; Using Index

When the query is running the server pegs 1 CPU Core at 100% (the other 7 remain idle). The tables are using InnoDB and I do have InnoDB set to 1 file per table.

Any ideas what the heck I am doing wrong?

UPDATE

I was on MySQL 5.5, I have updated to MariaDB 10.1.7 to give that a shot.  No change.  The fields in both tables are defined as varchar(9) utf8_general_ci NULLABLE.  The indexes are BTREE, not unique, not packed, NULL yes, Collation A. I moved the two tables in question to ARIA storage engine just to give that a shot as well, no change.

I also noticed mysqld is using almost no memory (thanks @Uueerdo).  I am wondering if this has something to do with it.  I am not sure what settings I need to change exactly.

I ended up moving the tables back to InnoDB and then increasing the innodb_buffer_pool_size to 8G.  It's still not sub 1 second like our MS SQL Server, but it is finishing in under 5 seconds, so a huge step in the right direction.


Permissions on sys.syslogins I've got the following scenario:
( loggend in on SQL-Server with full admin-privileges )

Creating LogIn, User, Role and associate them.

IF NOT EXISTS( SELECT "name" FROM "master"."dbo"."syslogins" WHERE "name" = 'ocmb_admin' ) BEGIN CREATE LOGIN ocmb_admin WITH PASSWORD = 'pw', CHECK_POLICY = OFF; END

IF NOT EXISTS ( SELECT 1 FROM "sys"."database_principals" WHERE "name" = N'ocmb_admin' ) BEGIN CREATE USER ocmb_admin FOR LOGIN ocmb_admin;END

if not exists ( select 1 from "sys"."database_principals" where "name" = N'ocmb_grp_admin' ) begin CREATE role "ocmb_grp_admin"; end

EXECUTE sp_addrolemember N'ocmb_grp_admin', N'ocmb_admin';

Works fine. Needed permissions on objects for created login ( ocmb_admin ) are granted in separate queries, not completely neccessary for this question.

Now, logging in with this login I have to query sys.syslogins which is possible but only returns data for current login and sa.

I tried several ways to grant explicit permissions to the group ( ocmb_grp_admin ), but none of them leaded to the fact, that I can query the complete content of the view.

I need to determine, whether some logins exist while logging in with an random account, which is assigned to the prior created role 'ocmb_grp_admin'. Permissions can and SOHULD be granted to this role, to avoid having to grant them for each new user.

What I tried so far:

A:

USE master
GO
GRANT VIEW ANY DEFINITION TO "<username>"

This one works, but is completely uncomfortable, because every user has to be added separately.

B:

USE master
GO
GRANT VIEW DEFINITION ON master.sys.syslogins TO ocmb_admin

Error - Definition privilege is not compatible with object.

C:

USE master
GO
GRANT VIEW DEFINITION ON master.sys.syslogins TO ocmb_grp_admin

Same as B

D:

USE master
GO
GRANT VIEW DEFINITION ON LOGIN::ocmb_admin TO ocmb_admin

Can be executed, if ocmb_admin is also created on master-db, but does not have any effects on querying.

Thanks for hints!

You can use SUSER_ID which will give you the SID from sys.server_principals regardless of metadata visibility

Also note that syslogins has been deprecated since SQL Server 2005


Is there any slowdown in casting from varchar to text? In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and they're binary-coercable. Would it be faster to do

SELECT varchar1
FROM table
WHERE varchar1::text = 'foo';

Or,

SELECT varchar1
FROM table
WHERE varchar1 = 'foo'::varchar;

All tests done on PostgreSQL 9.5. On the matter of the cast,

Sample Data

We first create a table with 100,000 rows with 3 varchar columns.

CREATE TABLE foo AS
  SELECT x::varchar AS x1, x::varchar AS x2, x::varchar AS x3
  FROM generate_series(1,1e6) AS gs(x);

In SELECT

Then we try to the casts,

SELECT x1::text, x2::text, x3::text FROM foo;
Time: 476.825 ms

SELECT x1::text, x2::text, x3::text FROM foo;
Time: 501.092 ms

SELECT x1, x2, x3 FROM foo;
Time: 360.493 ms

SELECT x1, x2, x3 FROM foo;
Time: 373.647 ms

Conclusion: substantial slowdown it's not a compile type coercion.

In WHERE clause

However, in the where conditional they seem to act effectively the same,

SELECT * FROM foo
WHERE x1::varchar = '5'::varchar;

Performs as well as,

SELECT * FROM foo
WHERE x1::text = '5';

And if I now run,

SELECT *
FROM foo
WHERE array_position(ARRAY['5', '6', '9'], x1::text) IS NOT NULL;

SELECT *
FROM foo
WHERE array_position(ARRAY['5', '6', '9']::varchar[], x1) IS NOT NULL;

Both of them perform the same, so there is an optimization in the WHERE clause.


why MySLQ datadir must be owned by mysql user? I read this from MySQL document:

  Otherwise, most of the MySQL installation can be owned by root if you like. The exception is that the data directory must be owned by mysql.

Can somebody tell me why?
Why data directory owned by root is not good?

When MySQL is running, it runs as the mysql user account. If the data directory was owned by root, MySQL would not have permission to write to it!


apache hang on deadlock i use code like this  to prevent deadlock.

while($retry<3 and $notsone)
{
try
{
$transaction->commit();
$notdone=false;
}
catch
{
$transaction->rollback();
$retry++;
}
}
if ($retry==3)
{
throw new exeption("deadlock found!");
}

i test code with running code in 6 different browser concurrently . sometimes in some browser i see a deadlock found message but sometimes i got a message from apache http server.
apache http server has stopped working. i have 2 option. close program or check for online solution.

my question is do concurrency problem cause apache hang?
how to prevant this?

MySQL has built in deadlock detection and detects a deadlock as soon as it occurs.  If you need to detect deadlocks, you will have to examine the error message coming back from the failed transaction, or use pt-deadlock-logger to log deadlocks external to the application.

The code you have supplied doesn't detect deadlocks.  It detects failed transactions and retries 3 times.  If the transaction took a long time, you will occupy an apache worker for three times as long as the transaction took.  If the failure occurs due to a lock timing out (note locks and deadlocks are very different), the code above could take up to 2.5 minutes to return to the browser, assuming you have innodb_lock_wait_timeout set to the default of 50 seconds.  That's a long long time.

Can we step back and ask what is it that you are trying to accomplish with this code path?

(side note, I think this belongs on stackoverflow, not here as this seems more like a PHP/development question).


Can Availability Groups provide seamless failover (with no query failures)? I have been testing the Availability Groups feature in SQL Server 2012 and am finding that there is approximately 15 seconds of down time when the primary server fails over to the secondary server. All SQL queries executed during this time are failing until the failover transition is complete.

Is there any way to get this down to 0 seconds and prevent queries from failing during the failover transition?

In other words, is there a way to get any queries running during the failure to be redirected to the primary server instead of failing... and is there any way to get new db connections to immediately connect to the secondary server instead of failing to connect during the failover transition?

I currently have 2 servers set up in the Availability Group.

No, there's no way to do zero-downtime failover with AlwaysOn (or in SQL Server in general, as far as I'm aware). To do that, the SQL Server you're connected to would have to do state transfer to another node mid-query, and since many failovers are unexpected, that's not possible.

However, you can enable "read-only secondaries" in AlwaysOn, and then your readers would have zero-downtime when the primary server fails over - since they're connecting to a secondary copy anyways to do their SELECT queries, they wouldn't even notice the failover. There would still be an interruption for users with a "Write" connection open, but at least some part of your user base would be uninterrupted.


PostgreSQL command history on Windows I am trying to use psql from a Windows command prompt (cmd.exe), but can't access any of the previous commands with the up/down cursors. I could also really do with finding out where the PostgreSQL equivalent to .bash_history is (if there is one), as I could read the command history from there.

\s produces the following error:

  history is not supported by this installation

Is there a way to get the psql command history on Windows?

On Windows, there isn't an exact equivalent to a .bash_history because cmd.exe is an intrinsically different type of shell. I think you're referring more to the .psql_history (see related question).

There is a "SQL Shell" available in the SQL Command Line Terminal of the EnterpriseDB Windows distribution of PostgreSQL that supports a command history. However, I prefer to use the Windows command prompt instead of the GUI.

The error message you are getting indicates that psql.exe was built without readline support (reference).

The best way I have found to run psql via the normal Windows command prompt is to create a shortcut:

Target: %SystemRoot%\system32\cmd.exe /U /c "chcp 1252 & C:\Progra~1\PostgreSQL\9.6\bin\psql.exe -U database_username -d database_name"

The command history can be controlled via Command history Buffer size in the Options pane:

And you can adjust the scrollable output via Screen Buffer Height in the Layout pane:

This will get you a history via the up and down arrows, but only within your current session. It will not be preserved across sessions.


Character Encoding in Postgres I have UTF8 data stored in Postgres.  I can query it with a Perl script and display it properly.  When I use the Postgres client, it displays like the encoding is off.  I'll see characters displayed like \u0087\u0081.

From inside the database:

=> \encoding
UTF8

In the shell (I manually set LANGUAGE as I read elsewhere that might be a fix.):

$locale
LANG=en_US.UTF-8
LANGUAGE=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

This is in Ubuntu 16.04.

What setting have I missed?

Edited to add code populating psql:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

### Source DB setup
my $maria_database="srcdb";
my $maria_user = "";
my $maria_password = "";
my $maria_host= "localhost";

my $mariadbh = DBI->connect("dbi:mysql:database=$maria_database;host=$maria_host","$maria_user","$maria_password",{AutoCommit=>1,RaiseError=>1,PrintError=>0,mysql_enable_utf8=>1});

my $mysrcquery = "SELECT fields from sourcetable limit 2;";
my $src = $mariadbh->prepare($mysrcquery);

### Destination DB setup
my $postgresql_database="desdb";
my $postgresql_user="";
my $postgresql_password="";
my $postgresql_host="localhost";

my $dbh = DBI->connect("DBI:Pg:dbname=$postgresql_database; host=$postgresql_host", "$postgresql_user", "$postgresql_password", {AutoCommit => 0,pg_enable_utf8 => 1});

my $pginsertquery = "insert into desttable (fields) VALUES (?)";
my $pginsert = $dbh->prepare($pginsertquery);

$src->execute();
my ($col0);
$src->bind_columns(undef, \$col0 );

### Loop through results and insert in to psql
while($src->fetch())
{
  print $col0;
  $pginsert->execute($col0);
}

$dbh->commit;
$dbh-> disconnect or warn "Disconnection failed: DBI::errstr\n";
$mariadbh-> disconnect or warn "Disconnection failed: DBI::errstr\n";

Test sqldump:

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.1
-- Dumped by pg_dump version 9.6.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test; Type: TABLE; Schema: public; Owner: testuser
--

CREATE TABLE test (
    testdata text
);

ALTER TABLE test OWNER TO testuser;

--
-- Name: test id; Type: DEFAULT; Schema: public; Owner: testuser
--

ALTER TABLE ONLY test ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);

--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: testuser
--

COPY test (testdata) FROM stdin;
Ð¿Ñ<80>Ð¸Ð²ÐµÑ<82>Ñ<81>Ñ<82>Ð²Ñ<83>Ñ<8e>
\.

--
-- PostgreSQL database dump complete
--

\u0087 (END OF SELECTED AREA) and \u0081 are control characters in the LATIN-1 block. Displaying them as \u0087\u0081 is in fact the convention, and it seems to be what your terminal is doing.

Can you show us the Perl script to insert the data into the database? My assumption is that Perl is inserting non-utf8 data because working with utf8 in perl is a PITA. That PostgreSQL assumes it's utf8 and that the terminal has no idea of how to display it, becuase it's

not really utf8, just marked up as utf8.
or, you have legitimate control characters encoded in your utf8.

Be careful if you used utf8::upgrade or the like which sets the flag without decoding from the original format.

Advice

Update DBD::Pg, DBD::mysql

First update both DBD::Pg, and DBD::mysql

cpan DBD::Pg DBD::mysql

Bug in DBD::mysql

Reviewing change logs, DBD::mysql has always had a bug, you may even want to install the development version (DBD-mysql-4.041_01)..

2016-12-12 Patrick Galbraith, Michiel Beijen, DBI/DBD community (4.041_1)
* Unicode fixes: when using mysql_enable_utf8 or mysql_enable_utf8mb4,
  previous versions of DBD::mysql did not properly encode input statements
  to UTF-8 and retrieved columns were always UTF-8 decoded regardless of the
  column charset.
  Fix by Pali Rohár.
  Reported and feedback on fix by Marc Lehmann
  (https://rt.cpan.org/Public/Bug/Display.html?id=87428)
  Also, the UTF-8 flag was not set for decoded data:
  (https://rt.cpan.org/Public/Bug/Display.html?id=53130)

Who knows what the hell they're doing there. Unicode is complex and it's not surprising that MySQL developers and users don't understand it.

Unsetting assumptions

I would unset pg_enable_utf8 and mysql_enable_utf8. Pretend these options exist. You're force-setting a utf8 flag on the internal variable. You probably don't want to do that. You clearly can't ensure everything is utf8, or it'd be working properly. Time to remove assumptions. Not create more assumptions.

We need a sample case. You claim foreign characters are displaying as Unicode escape sequences, but you only present control-characters (which can't be displayed). Perhaps you can simply this whole thing, and dump the table pg_dump -t <Table> or copy the problematic rows into a temp table and dump that. Then we can review that data and see what we've got.


How can one identify if FK's needed to be indexed apart from the NC indexes already residing I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done.

I have a similar env where there lots of reads for this one particular database. As a good process, I am trying to find the Foreign Keys which can be indexed , even though NC' indexes are there for those tables.

So, is there a way I can identify those FK's which needs to be indexed, making sure it does not overlap with NC indexes, already existing on those tables for that particular database.

The database in question has quite a number of FK's and queries with lots of joins, comprising a good no of tables. This DB is 95% involved in read and activity and once in a month a file is uploaded which updates the tables.

+Info- We are using SQL server 2012.

Please suggest for any such script or a better way to approach this, thanks!

There are benefits to indexing foreign keys as they provide better join performance as described by SQLSkill's team here and here.

  It is generally recommended to create an index which leads on the foreign key column(s), to support not only joins between the primary and foreign keys, but also updates and deletes.

A script can be found here or Indexes Supporting Foreign Keys.

Review any index recommendation that the scripts give and make sure you dont end up created a lot of indexes since there is always a cost of maintaining them.

Also, make sure that your FK's are trusted.


Ola Hallengren script not deleting old backup files but simply taking time We recently migrated to a new SQL Server and the SQL Services are running under a Service Account. I did observe that the Service Account do not have FULL rights on G drive and the old files are not being cleaned up.

Question: Why doesn't the xp_delete_file procedure Error out when it cannot delete a file OR simply move on instead of waiting for around 9.5 minutes?

Also, doesn't the job error if it cannot delete the old backups? We will not know until we receive LOW DISK SPACE alert !!

Here is the log details.

Date and time: 2017-05-23 02:02:17
Command: BACKUP LOG [DBName] TO DISK = N'G:\SQLBackups\ServerName\DBName\LOG\ServerName_DBName_LOG_20170523_020217.trn' WITH CHECKSUM, COMPRESSION
Processed 17 pages for database 'DBName', file 'DBFileName_Log' on file 1.
BACKUP LOG successfully processed 17 pages in 1.206 seconds (0.110 MB/sec).
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2017-05-23 02:02:18

Date and time: 2017-05-23 02:02:18
Command: RESTORE VERIFYONLY FROM DISK = N'G:\SQLBackups\ServerName\DBName\LOG\ServerName_DBName_LOG_20170523_020217.trn' WITH CHECKSUM
The backup set on file 1 is valid.
Outcome: Succeeded
Duration: 00:00:02
Date and time: 2017-05-23 02:02:20

Date and time: 2017-05-23 02:02:20
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'G:\SQLBackups\ServerName\DBName\LOG', 'trn', '2017-05-16T02:02:20' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
Outcome: Succeeded
Duration: 00:09:28
Date and time: 2017-05-23 02:11:48

P.S. The Service Account was granted FULL rights and the old files are being cleaned up.

Version Info: SQL Server 2012 SP3 CU8 / Windows Server 2012

You're providing xp_delete_file with a directory, and a file extension to check for. How many files were there that needed to be deleted when this took 9.5 minutes to run?

It would make sense that the process would be trying to delete each file, and would ultimately fail to delete each file. If there's one file to try to delete, it would complete much faster than if there's 100.

To try to confirm this, I created a directory in which I placed (initially) 1 .bak file dated 2017-03-01, set it to "read-only" (so it shouldn't get deleted), and ran the following to try to delete it.

EXECUTE master.dbo.xp_delete_file 0,N'H:\Dummy_Backup',N'bak',N'2017-04-27T14:09:10'

With 1 file, the command completed successfully (but no files were deleted) in less than 1 second.

I made copies of the file, so I had 64 files, and ran again. This time, it took 1 second.

Continuing in the same vein:

256 files - 5 seconds
512 files - 15 seconds
1024 files - 31 seconds

So, it seems likely that there were a large number of files it was trying (and failing) to delete.

And, to be clear - it failed to delete any files in all of the above tests as well. I went in and turned off the "read-only" attribute for the 1024 files after the last test above, and ran it again - all 1024 files were deleted successfully (in 25 seconds).

So, it does seem likely that the 9.5 minute wait was due to a large number of files it was trying to delete.


Split Backup of MSSQL VS Stripe Backup of Sybase ASE I recently came to know about concept of split backup in MSSQL Server whereas I have been working on stripe backup in Sybase ASE.

Stripe backup in Sybase ASE is faster than normal backup and if we use three stripes then Sybase will use 3 cores of CPU(Depends on number of cores) as it will be split into that many threads, are performed in parallel irrespective of storage location.

I wanted to understand if same is the case with MSSQL split backup, I read that if we use different locations for split backup then the backup will be performed in parallel and will be faster:

Split Backup

however if storage location is same( In the same drive and folder), no extra IO involved and hence not much impact on performance.

Any speedup in a striped backup will come from using multiple IO channels.  If all the files are in the same storage location it won't be any faster.

Using multiple CPU cores to process a backup probably won't help, as backup is extremely simple.  And a single CPU core can saturate the storage subsystem with backup pages.

You can see this for yourself with any big database, by writing the backups to the nul device:

set statistics time on
go

print '

single backup'

backup database AdventureWorks
to disk = 'nul'
go

print '

striped backup'

backup database AdventureWorks
to disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul'

outputs

single backup

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Processed 651384 pages for database 'AdventureWorks', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks2017_log' on file 1.
BACKUP DATABASE successfully processed 651386 pages in 2.837 seconds (1793.779 MB/sec).

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 2877 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

striped backup

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Processed 651384 pages for database 'AdventureWorks', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks2017_log' on file 1.
BACKUP DATABASE successfully processed 651386 pages in 4.023 seconds (1264.964 MB/sec).

 SQL Server Execution Times:
   CPU time = 499 ms,  elapsed time = 4123 ms.


Suitable way to record users requests for usage invoicing/billing in database? Is there more efficient way to keep a track of all requests made by each user with timestamp? And then filter the requests every fortnight to invoice the users?

Example DB schema

Table: user; Attributes: UserID (PK)

Table: item; Attributes: ItemID (PK)

Table: user_request; Attributes: UserID, ItemID, Timestamp

where UserId and RequestID is composite key

How should one invoice requests made by users.
Is the only way to filter the stored requests based on invoice period and count the records within that period? Is there a more efficient/suitable way to invoice the requests?

What DB design are other services that charge users made on usage rather than subscription that charge on a set num of requests.
Please note I'm referring to requests for data. The service would be charging users based on number of requests made for data.

For a simple solution, you can add a nullable foreign key to an INVOICE table to your USER_REQUEST.  If you want to know what needs invoicing, just select everything for a given user that has a NULL foreign key.

When you create the invoice, set the foreign key to point to the new invoice.

For a more complex, but more sophisticated solution, you may want to consider an intersection table between the USER_REQUEST and an INVOICE_ITEM table.  This solution will recognize and handle the situation where an item is charged on one invoice, then returned on a credit note, and possibly even re-invoice again, and so on...


Missing FROM-clause in RETURN QUERY EXECUTE FORMAT loop This query works fine, but I need to execute it for multiple tables:
select 'service:dms_fdo_query:joined' as metric_name, hypertable_size('prom_data."service:dms_fdo_query:joined"') as raw_size

I wrote this function:
CREATE OR REPLACE FUNCTION public.get_metric_sizes()
  RETURNS TABLE(metric_name text, raw_size bigint) AS
$func$
DECLARE
   metric_table name;
BEGIN
   FOR metric_table IN SELECT table_name FROM prom_info.metric
   loop
    RETURN QUERY EXECUTE FORMAT('SELECT %L as metric_name, hypertable_size(prom_data.%I) as raw_size', metric_table, metric_table);
   END LOOP;
END
$func$  LANGUAGE plpgsql;

but I am getting this error:
SQL Error [42P01]: ERROR: missing FROM-clause entry for table "prom_data"
  Where: PL/pgSQL function get_metric_sizes() line 7 at RETURN QUERY

I have really no idea how to fix this, any suggestion would be greatly appreciated!

It turns out that I simply needed to quote the table name passed to the function:
RETURN QUERY EXECUTE FORMAT('SELECT %L as metric_name, hypertable_size(''prom_data.%I'') as raw_size', metric_table, metric_table);


SQL 2005: logical consistency-based I/O error: incorrect checksum I got this error this morning during the execution of a SSIS package:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x5fb84eb5; actual: 0x7fb84eb5). It occurred during a read of page (1:6434165) in database ID 8 at offset 0x00000c45aea000 in file 'D:\Data\REPORTING.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I ran DBCC CHECKDB and it found no errors:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'REPORTING'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

However, I do see an entry for that page_id in the suspect_pages table.

Do I have a problem or not?

Yes, although the problem may be memory. I suggest you upgrade SQL Server 2005 to the latest release, and if that is check the CU upgrade level at SQLRELEASESERVICES (Type it into google - it ia blog by the team releasing monthly (!) updates). Maybe you also run into an issue on the sfotware side.


PostgreSQL skips WITH query Consider this CTE:
WITH division_by_zero AS (
  SELECT 1/0
)
SELECT 42

It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?

That is because the main query doesn't reference division_by_zero anywhere.
The documentation says:

[...] PostgreSQL's implementation evaluates only as many rows of a WITH query as are actually fetched by the parent query.

That is zero in your case.
If you want the CTE to be executed, you could for example add a WHERE condition like
WHERE EXISTS (SELECT 1 FROM division_by_zero)


Optimizing index performance for mysql The mysql version that I use is :

'aurora_version','1.17.98'
'innodb_version','1.2.10'
'protocol_version','10'
'slave_type_conversions',''
'version','5.6.10-log'
'version_comment','MySQL Community Server (GPL)'
'version_compile_machine','x86_64'
'version_compile_os','Linux'

This is the db table structure (relevant parts) that I m working with (table name access_logs) :

id| EventTime | EventType | EventName ... | event_log_domain_id

the table has approximately 11mil records.

These are the common ways that the data is queried :

1) Using in statement with event_log_domain_id and performing count

SELECT COUNT(*) FROM `access_logs ` WHERE `access_logs`.`event_log_domain_id ` IN (8, 59, 920, 1054, 2227) AND `access_logs `.`EventType ` = 1

2) Not Using in statement with event_log_domain_id and performing count

SELECT  COUNT(*)
    FROM  access_logs USE INDEX(event_log_domain_id)
    WHERE  `access_logs`.`event_log_domain_id ` = 1304
      AND  `access_logs`.`EventType ` = 1

3) Not performing the count

SELECT  `access_logs `.* FROM access_logs WHERE `access_logs `.`event_log_domain_id ` = 1304 AND `access_logs `.`EventType ` IN (1, 5)  ORDER BY id desc LIMIT 50

I have indexes on both event_log_domain_id and EventType.

Count performance is by far the worst. It seems like count queries don't even use the indexing. Even when I provide the index name, for ex :

SELECT  COUNT(*)
    FROM   `access_logs` USE INDEX(event_log_domain_id)
    WHERE  `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227 )
      AND  `access_logs`.`EventType` = 1

Does not help improve the performance much, may even make it worse.

On the other hand the select query(#3) does perform better when providing USE IDEX.

I was also thinking about adding composite index on both event_log_domain_id and EventType. Rebuilding indexes takes somewhat long so I don't want to just randomly try stuff out without understanding the performance implications first.

Is there a special index I can create for the count operations? At this point for the worst case scenario the query #1 and #2 runtime is about 240 sec or 120 each.

Beware!  You have an extra space in some table and column names.  (I removed them when wrapping the queries.)

This is the optimal index for all those queries, and there is no benefit in saying USE INDEX:

INDEX(EventType, event_log_domain_id)  -- in this order
INDEX(event_log_domain_id, EventType)  -- in this order for #3

This index will handle the WHERE and be "covering".  Include both indexes.

More on indexing:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql  It explains how the composite index should start with all columns tested with =, then move on to IN, and finally one "range".  (Your examples have no ranges).


How to deal with `.partial` WAL segments generated by Postgres `pg_receivewal` command? pg_receivewal creates a .partial file for incomplete WAL segment. My question is how to deal with this file during recovery. If I just leave it as it is, last transactions don't recover. If I rename the file removing .partial post-fix, the recovery procedure crashes with the error:

FATAL:  archive file "000000010000000C00000080" has wrong size: 152 instead of 16777216

The only way I found is to remove this .partial segment after the previous recovery crash and start the procedure again. This way db seems fully recovered.

What the proper way to deal with these .partial segments?

My db version is 11.5.

Update:

Following Laurenz advice I ended up with running next command right before starting the recovery.

it testes if there's no another version of the partial segment
decompresses it (as I use pg_receivewal with --compress flag)
augments it with nulls
and copies the fixed segment into pg_wal folder

find /backup/wal -name "*.partial" -exec sh -c ' \
    NAME=`basename "$0" .gz.partial`; \
    DEST=$PGDATA/pg_wal/$NAME; \
    test ! -f $DEST && \
    zcat $0 > $NAME && \
    truncate -s16M $NAME && \
    mv $NAME $DEST \
' '{}' \;

Notice it's relying on $PGDATA environment variable which usually isn't set.

Hmm, good question.

I suggest that you rename it and fill it with zeros at the end, for example using

dd if=/dev/zero of=000000010000000C00000080 oflag=append conv=notrunc bs=1024 count=16416

This could be part of your restore_command.


Querying an ordered list with OR or IN clause I am querying items from a database and display it to the user. (in my example I'll use contacts in table tContacts.

The user selects a couple of items in an order he decides (the user can random pick the items, so no column can be specified to order).

In the application I have the id's of those items in an ordered list.

Currently I query more details for those objects one-by-one.

Is it possible to create a query with an "ordered list", which returns the items in the same order?

something like (yes, this is ugly - probably also hitting the max sql query size):

SELECT * FROM (
    --how to append this list...
    SELECT 0 as listIndex, * from tContacts where conIdContact = 13259;
    SELECT 1 as listIndex, * from tContacts where conIdContact = 12472;
    [...]
    SELECT 568 as listIndex, * from tContacts where conIdContact = 12422;
)
ORDER BY listIndex

OR: do i have to keep querying one-by-one
OR: does this need a temp table (insert id and priority), then query, then drop temp table again.
OR: query a batch and reorder the items in application code
(this might be slow due possibly large lists)

note: a database independent solution is preferred (mariadb / mysql, postgresql and h2 are considered to be used in production.

my temp table concept:

CREATE TEMPORARY TABLE temp_OrderedContacts(
    listIndex INT NOT NULL DEFAULT 0,
    contactId INT NOT NULL DEFAULT 0
);

INSERT INTO temp_OrderedContacts (listIndex, contactId)
VALUES (0,13259), (1,12472), (2,12422);

SELECT * FROM tContacts
JOIN temp_OrderedContacts ON (tContacts.conIdContact = temp_OrderedContacts.contactId)

DROP TABLE temp_OrderedContacts;

A Postgres solution would be to put all the IDs into an array, then use something like this:

select t.*, il.listindex
from contacts t
   join unnest(array[13259, 12472, ..., 12422]) with ordinality as il(id, listindex)
     on t.conidcontact = il.id
order by il.listindex;

(The above is actually standard ANSI SQL - but to my knowledge only Postgres and HSQLDB support that)

The array could also be passed as a parameter in a prepared statement (or whatever the term for that is in the programming language you are using).

If you can build a dynamic query, another option would be to use a row value constructor:

select t.*, il.listindex
from contacts t
   join (
      values
        (1, 13259),
        (2, 12472),
        .....
        (568, 12422)
  ) as il(listindex, id) on t.conidcontact = il.id
order by il.listindex;

(The above is 100% standard ANSI SQL)


oracle: deny naked sql statements I'm looking for a solution in oracle 12c to deny INSERT, UPDATE and DELETE statements on tables.

I would only provide SELECT privileges and exec rights to execute procedures which INSERT, UPDATE or DELETE rows from tables. So I can ensure clean rows.

It there a way to implement triggers to deny naked sql statement access?

The account that owns the database objects should not be used by anyone to access that data.  (Except by you and your fellow DBAs, who know what they're doing!)

A simple, "additive", security model is far easier to understand and maintain.

Create "Application" and "User" roles and grant the appropriate levels of access, then grant these to user and application accounts.

create user data_owner ... ;
create table data_owner.table1 ... ;

create role app_role_1 ;
grant select, insert, update, delete on data_owner.table1 to app_role_1 ;

create role user_role_2 ;
grant select on data_owner.table1 to user_role_2 ;

grant app_role_1 to application_1_account ;
grant user_role_2 to fred, wilma ;


How to use wal_level in Postgres to set the recovery mode to simple for a database like how Microsoft SQL Server does it? In Microsoft SQL Server, ALTER DATABASE CURRENT SET RECOVERY SIMPLE WITH NO_WAIT; will set the recovery mode for a database to simple, so the transaction log file space can be re-used.
In Postgres, wal_level is available for that purpose.

Is wal_level's minimal equivalent to SQL Server's RECOVERY SIMPLE?
How to set wal_level in Postgres command? - so far, Google Search tells me that wal_level is set in a configuration file.
Is it possible to set wal_level to minimal for a database? If yes, how?

Thank you.

PostgreSQL works quite differently. Transaction log files will be removed with all wal_levels (as soon as the file is full and archived and a checkpoint has completed).
If you want to live without WAL archives, and all you need is crash recovery, you can set wal_level to minimal.


ORA-00449: background process 'MMAN' unexpectedly terminated with error 7446 One of our Oracle servers went down rather badly last night, and we have been unable to bring it online since. Attempting to startup each instance results in:

SQL> Connected to an idle instance.
SQL> ORA-00449: background process 'MMAN' unexpectedly terminated with error 7446
ORA-07446: sdnfy: bad value '' for parameter .
SQL> Disconnected

After running this, none of the instances are online. The listener reports that is running, at least.

The first instance (asdb) in the order has a number of (mostly repetitive sections) in its alert.log, but nothing in there seems particularly likely. The other instances have blank (but writeable) alert.log files.

Where should I investigate to try and get this back online?

For some reason, it seems that the bdump and cdump directories for each instance had been deleted. I don't know who (or what) did that, but restoring them fixed the problem.


Distinct Combination of Two Columns I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other.

The data is all around backup policies being run on against particularly clients and could be summarized as below:

The answer I would like to generate for the above table would be 7 as there are that number of distinct combinations.

Has anyone got an idea of how this might be done? I have tried experimenting with nested counts and distinct values (where I was able to filter to one column but not both).

You can count distinct elements by running:

select count(distinct policy_id, client_id) from policy_client;

Another option would be to group by and count that:

select count(*) from (select policy_id, client_id from policy_client group by 1,2) a;

Run both version and see which one performs better on your dataset.

A very quick way but not totally accurate if you have a key on (policy_id and client_id) you can also check the cardinality of that index but that's an approximate not exact number.


MySQL query with an OUTFILE to csv not working when called from a batch file When I execute the following query, it works:

SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

But when I call this query from the Windows command prompt or from a batch file using the following command:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/Stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"

it does not run. Here's what happens:

C:\Documents and Settings\188931>"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "
%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'foldrname/
orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe  Ver 14.14 Distrib 5.5.16,
 for Win32 (x86)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe [OPTIONS] [database
]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)

Another thing I noted is, when I run the following:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS';"

from the command prompt or a batch file, it does execute. (Note - I removed the csv export).

So it seems a combination of executing a query from batch file (or Windows command prompt) which is having a OUTFILE to csv is not working.

Could any body kindly help on this?

I bet that the double quote is the culprit: it terminates the string at ENCLOSED BY ', and the rest derails the whole thing.  The solution is very simple: just escape the double quote in your SQL string:

... ENCLOSED BY '\"' ...

(I have to tell that on my local Windows box and psql (instead of mysql) client I don't have to escape the double quote.)


How should a numeric value that is an external key be stored? This is something I was pondering recently. trying to determine how to store certain values. The best wide spread example I can think of is Federal Employee Identification Number - more commonly knows as tax id or social security for companies.

For various business / tax / legal reasons we need to store this number for some of our clients.  Now this is a numeric 9 digit field .  However it doesn't change. There are no calculations to be done with it, this is an external reference field that we have no control over.  So should it be stored as char(9) or int(9) ?  What are the benefits of storing one way or another? What's the most efficient way: in terms of space usage(I'm assuming int) , and in terms of performance ( speed of look ups) assuming that we will create a unique index on this column either way?

For the purpose of this exercise DB doesn't matter, but if want to elaborate I use MariaDB cluster ( MySQL)

Before start, please note that external keys are usually bad as primary keys, because they are larger than needed, they can change and some people may not have one, or have duplicated ones (even if legally that shouldn't happen).

Speaking correctly, the EIN is a code of 9 digits. As such, the technically correct value should be a string. In particular, for MySQL (yes, the database and storage engine matters a lot, although not so much between MySQL or MariaDB), that should be (I'm assuming it is a not null field) a:

CHAR(9) charset ascii NOT NULL

That should only take 9 bytes of fixed space in most engines. Please remember to use ascii, and not other charset, as you may waste 3 or 4 bytes per character.

The reason to check if logically a number should be an integer or a string is checking if it has to be used in arithmetic operations and the ordering. If someone asks you to order the EINs, '000-00-0009' should be before '100-00-0000', so zeros are important.

Having said that, using something like

int(9) UNSIGNED ZEROFILL NOT NULL

Would take you less size (4 bytes) and on the command line client it will show you the preceding zeros:

mysql> SELECT * FROM ein;
+-----------+
| ein       |
+-----------+
| 123456789 |
| 000000001 |
+-----------+

One common misunderstanding of int(NUMBER), is that we are telling the integer that it can only be up to 9 digits. That is incorrect. An int will always take 4 bytes and, in the case of an unsigned int, it will take values from 0 to 4,294,967,295. Most applications will ignore the size anyway.

Less space means less amount of bytes read and written to disk, and more free space on memory. And even if 4 to 9 bytes may not be a lot, if you have 200 million records, that is 1GB less of information (more if you have into account indexes). If you do not have a large amount of records, that may not affect you so much, although depending on the operations performed (it may involve transforming it into a character), both approaches may have its downsides.

There is a last option, probably way worse in performance, but that could help with consistency. MySQL has the "NUMERIC/DECIMAL" datatype, so you could also define it as:

ein decimal(9,0) zerofill unsigned not null

That should only take 4 bytes in MySQL, but it disallows both non-number characters (something that char allows) and numbers > 999,999,999 and <0 (unlike int), so it may be a bit less error-prone, but usually fixed-point arithmetic takes more cpu cycles than simple integers.

In general, performance issues can only be checked once you have the whole system running– sometimes trying to over-optimize is a mistake if there are not so many records and it makes working with them more complicated. Stick with the easiest one at first and optimize later.


SQL Server 2005 - 2 sets of backups - 2 maintenance plans? I did my best to search for an answer to this question and couldn't find a scenario quite like this.

I'm a fairly new DBA in the sense that I haven't had a ton of training, I basically took the job over when a co-worker left.

This question pertains to our server that's running SQL Server 2005.

Recently I've been trying to make sure that our data is as safe as possible and thus want to have two backup sets in two different places.  I currently have one set of plans doing a full backup, system DB backup and hourly transaction log backups to one server and then another set of plans doing the same thing but storing them on another server.  The transaction log portions of the plans are also staggered by 30 minutes so that in the event I need to restore I can choose the most recent.

Is this the proper way to go about doing this?  I've read that backup striping is usually done via a backup script and it sounds like the maintenance plan in 2005 isn't capable of doing this. Is that the case?  Can anyone say if my method is usable or could this create problems in the future?

Thanks for your help.

You probably don't want to put your backups in two places like that alternating.  You (probably) won't be able to do a point-in-time restore if one of your file store locations is lost.  You'll be stuck with the latest full backup of the remaining system.

DMason's suggestion is workable, but personally I would use the scripts from http://ola.hallengren.com/ and back up to a network location.  From there, you can backup up the files to tape or another disk.


Why does the plan cache contain estimated and not actual execution plans? I can think of many reasons behind this decision of storing estimated plans in the plan cache and not the actual plan. But I can't find the "correct" answer.

Think about what "actual" means. It's what actually happens for the execution of that plan.

Another common name for the actual execution plan is the "post execution plan".  As a real world example to correlate this scenario, say you plan to go on a cross country trip, so you plot out the roads you're going to take and how long you think it'll take. But because of road work and detours, it doesn't happen quite like you planned. That's the actual execution plan:  The actual route that you took to get to where you're going.  The estimated plan would have been what you thought you'd be doing, and what route you thought you should take.

You can't store post execution plans because they are just that....post execution. They contain metrics of what really happened, not what SQL Server thinks should happen (estimated execution plan).

The plan cache is really just a repository of "maps" that it can reuse without having to spend the expensive time to draw up a new one. Actual execution plans are after the fact, and the plan cache is to handle before the fact.


pgAdmin crashes or freezes when big MATERIALIZED VIEWs are refreshing I'm using pgAdmin III v1.22.1 on Windows 7 to connect to a PostgreSQL server version 9.4.8 on Red Hat.

We have scripts to REFRESH MATERIALIZED VIEWS during the night.
When they are refreshing, I can't connect to pgAdmin III. It just freezes. It seems that it tries to query some locked objects.

When the materialized views finishes refreshing, I am then able to log in via pgAdmin.

When the REFRESH MATERIALIZED VIEW my_view is done, the above query is run and pgAdmin unfreezes.

Is there a way to prevent pgAdmin from freezing when MATERIALIZED VIEWS are refreshing?

I seems that it's a known bug.
I'm answering to my own question with this workaround.

If you use PostgreSQL 9.4+, one workaround is to use REFRESH MATERIALIZED VIEW CONCURRENTLY.

From PostgreSQL's manual:

  CONCURRENTLY

  Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which
  affects a lot of rows will tend to use fewer resources and complete
  more quickly, but could block other connections which are trying to
  read from the materialized view. This option may be faster in cases
  where a small number of rows are affected.

  This option is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all
  rows; that is, it must not index on any expressions nor include a
  WHERE clause.

You just need to add an UNIQUE INDEX to your materialized view and to refresh it CONCURRENTLY:

-- add index (do it only once per matview)
CREATE UNIQUE INDEX ON very_big_matview (id);
-- refresh concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY very_big_matview;

But it does not prevent other users from running long refreshes which hangs my pgAdmin.


Better ways than rownum limits on a union all for fallback-style queries? I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to merge them as one.

I am considering a construct like this:

select * from (
 <query 1>
 union all
 <query 2>
 union all
 <query 3>
) where rownum = 1

The explain plan cost for this seems to be the sum of the three queries, but actually running the construct seems to only incur the cost of the query(/-ies) necessary to produce the row (as explained on AskTom).

Is this a good way to do this? Is there any better alternative?

Maybe like this:

select * from (
 Select ... from ... where rownum = 1
 union all
 Select ... from ... where rownum = 1
 union all
 Select ... from ... where rownum = 1
) where rownum = 1

If you want to retrieve only one row of each table there is no need to read any more and limit the rows as early as possible.


If an INNER JOIN in a query returns no rows, do additional INNER JOINS add a significant performance overhead? If I make a query as follows:

SELECT *
FROM TableA A INNER JOIN TableB B ON A.AID = B.AID
  INNER JOIN TableC C ON B.BID = C.BID

Does the INNER JOIN with TableC add significantly to the performance of the query? Will it attempt to do the join, or will it note the empty set of records and move on?

The order in which tables are joined isn't defined in the query (unless you use a join hint, which I wouldn't recommend), so you can't be sure that A is joined to B before B is joined to C.

To answer your question, in most cases SQL Server can tell from the statistics on the table (or other criteria) that the query won't return any rows. In that case, another JOIN won't make any difference from a pure performance perspective.


Setting up a table with a calculated column I'm setting up a table in sql server 2008. It going to hold three columns (busTime, trainTime, carTime). I want these columns to hold the amount of time a user spends traveling per day. I then want to set up a calculated column that will work out the total time per day.

My question is what data type should I use for the three columns and if it's a weird data type that I've not used before (like "time") what would be the calculation that I would use on my calculated column.

Cheers

James

Why not just a smallint to represent "minutes"?
This is easily translated or formatted (in client code) or SUMmed etc

The actual conversion to minutes can be done in client code or as a DATEDIFF between start and end. I wouldn't use time personally for this because it won't process well (eg can't have > 23:59:59.9999999 if you want to see the monthly time


You need to change your subquery approach slightly - moving the condition from the WHERE clause to a join, to bypass MySQL limitations.

If the id and creation_time always define the same ordering, you can use this:

DELETE p
FROM
       pixels AS p
   JOIN
       ( SELECT id
         FROM pixels
         ORDER BY id
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.id < lim.id ;

Since that may not be true, and the two orderings may sometimes differ, resulting in deleting more or fewer rows than 5, you could use this, which will be more accurate but probably slower with a big table:

DELETE p
FROM
       pixels AS p
   JOIN
       ( SELECT creation_time, id
         FROM pixels
         ORDER BY creation_time DESC, id DESC
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.creation_time < lim.creation_time
     OR p.creation_time = lim.creation_time AND p.id < lim.id ;


Scalar function used in calculated column, what's the cleanest way to update the function? I want to update a scalar function in our database.  This function is used for several calculated columns in a key table so I get a dependency error if I try to update it.  I can explicitly remove the columns, update the function and re-add the columns but, without a lot of extra fannying about, that will change column orders and might have other unintended consequences.  I wondered if there was a cleaner way of doing it?

This may be more work but it should get (approximately) what you are after:

1 - Create a new table that has just your PK from the main table and the updated function.

2 - Drop your old column

3 - Rename your old table to something like TableName_Base

4 - Create a VIEW that joins your main table to your lookup table, with the fields in the order that you want to see them.  Name the view whatever your original table name was.

This will potentially create some issues with INSERTS and UPDATES though since you'll be accessing via a view.  To deal with that you can either insert into the table directly, bypassing the view.


SQL data deletion alert I want to know if some data has just been deleted from my database and receive an e-mail alert with the table names which have been affected by the deletion .

I have set up SQL SERVER to send e-mails, but i do not know how to get the data as explained above and the immediate sending of the e-mail as well .

You can use SQL Server Change Data Capture  or Change Tracking features to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

To get the changed records for a table and information about these changes, use the CHANGETABLE(CHANGES…) function.

Drawback: is that you have to query the tables periodically and then send the notification email

Create triggers on a table that needs auditing. Triggers will be fired when a record is inserted, deleted or updated and msdb.dbo.sp_send_dbmail should be used to send an email alert to a specified email recipient, immediately:

e.g.

CREATE TRIGGER t_Pers ON Person.Person
AFTER INSERT, UPDATE, DELETE
AS EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profie name',
@recipients = 'abc.xy.com' ,
@body = 'Data in AdventureWorks2012 is changed',
@subject = 'Your records have been changed'
GO

As triggers are fired per batch, not per row, it should be ensured that all batch transactions are captured, and no data is lost.

Therefore as mentioned above both the above solutions are very much helpful, but depends upon how you want to use them and get benefited.

In addition, please read Tracking Data Changes from MSDN and Performance Tuning SQL Server Change Tracking

The method which i have been using for such scenarios is analyzing the changes from default trace file which tracks object level changes using the below query as explained in this great article:The default trace in SQL Server - the power of performance and security auditing

    SELECT  TE.name ,
    v.subclass_name ,
    DB_NAME(t.DatabaseId) AS DBName ,
    T.NTDomainName ,
    t.NTUserName ,
    t.HostName ,
    t.ApplicationName ,
    t.LoginName ,
    t.Duration ,
    t.StartTime ,
    t.ObjectName ,
    CASE t.ObjectType
      WHEN 8259 THEN 'Check Constraint'
      WHEN 8260 THEN 'Default (constraint or standalone)'
      WHEN 8262 THEN 'Foreign-key Constraint'
      WHEN 8272 THEN 'Stored Procedure'
      WHEN 8274 THEN 'Rule'
      WHEN 8275 THEN 'System Table'
      WHEN 8276 THEN 'Trigger on Server'
      WHEN 8277 THEN '(User-defined) Table'
      WHEN 8278 THEN 'View'
      WHEN 8280 THEN 'Extended Stored Procedure'
      WHEN 16724 THEN 'CLR Trigger'
      WHEN 16964 THEN 'Database'
      WHEN 16975 THEN 'Object'
      WHEN 17222 THEN 'FullText Catalog'
      WHEN 17232 THEN 'CLR Stored Procedure'
      WHEN 17235 THEN 'Schema'
      WHEN 17475 THEN 'Credential'
      WHEN 17491 THEN 'DDL Event'
      WHEN 17741 THEN 'Management Event'
      WHEN 17747 THEN 'Security Event'
      WHEN 17749 THEN 'User Event'
      WHEN 17985 THEN 'CLR Aggregate Function'
      WHEN 17993 THEN 'Inline Table-valued SQL Function'
      WHEN 18000 THEN 'Partition Function'
      WHEN 18002 THEN 'Replication Filter Procedure'
      WHEN 18004 THEN 'Table-valued SQL Function'
      WHEN 18259 THEN 'Server Role'
      WHEN 18263 THEN 'Microsoft Windows Group'
      WHEN 19265 THEN 'Asymmetric Key'
      WHEN 19277 THEN 'Master Key'
      WHEN 19280 THEN 'Primary Key'
      WHEN 19283 THEN 'ObfusKey'
      WHEN 19521 THEN 'Asymmetric Key Login'
      WHEN 19523 THEN 'Certificate Login'
      WHEN 19538 THEN 'Role'
      WHEN 19539 THEN 'SQL Login'
      WHEN 19543 THEN 'Windows Login'
      WHEN 20034 THEN 'Remote Service Binding'
      WHEN 20036 THEN 'Event Notification on Database'
      WHEN 20037 THEN 'Event Notification'
      WHEN 20038 THEN 'Scalar SQL Function'
      WHEN 20047 THEN 'Event Notification on Object'
      WHEN 20051 THEN 'Synonym'
      WHEN 20549 THEN 'End Point'
      WHEN 20801 THEN 'Adhoc Queries which may be cached'
      WHEN 20816 THEN 'Prepared Queries which may be cached'
      WHEN 20819 THEN 'Service Broker Service Queue'
      WHEN 20821 THEN 'Unique Constraint'
      WHEN 21057 THEN 'Application Role'
      WHEN 21059 THEN 'Certificate'
      WHEN 21075 THEN 'Server'
      WHEN 21076 THEN 'Transact-SQL Trigger'
      WHEN 21313 THEN 'Assembly'
      WHEN 21318 THEN 'CLR Scalar Function'
      WHEN 21321 THEN 'Inline scalar SQL Function'
      WHEN 21328 THEN 'Partition Scheme'
      WHEN 21333 THEN 'User'
      WHEN 21571 THEN 'Service Broker Service Contract'
      WHEN 21572 THEN 'Trigger on Database'
      WHEN 21574 THEN 'CLR Table-valued Function'
      WHEN 21577
      THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
      WHEN 21581 THEN 'Service Broker Message Type'
      WHEN 21586 THEN 'Service Broker Route'
      WHEN 21587 THEN 'Statistics'
      WHEN 21825 THEN 'User'
      WHEN 21827 THEN 'User'
      WHEN 21831 THEN 'User'
      WHEN 21843 THEN 'User'
      WHEN 21847 THEN 'User'
      WHEN 22099 THEN 'Service Broker Service'
      WHEN 22601 THEN 'Index'
      WHEN 22604 THEN 'Certificate Login'
      WHEN 22611 THEN 'XMLSchema'
      WHEN 22868 THEN 'Type'
      ELSE 'Hmmm???'
    END AS ObjectType
 FROM    [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                        value
                                                FROM    [fn_trace_getinfo](NULL)
                                                WHERE   [property] = 2
                                              )), DEFAULT) T
    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
    JOIN sys.trace_subclass_values v ON v.trace_event_id =   TE.trace_event_id
                                        AND v.subclass_value = t.EventSubClass
      WHERE   TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )
            -- filter statistics created by SQL server
    AND t.ObjectType NOT IN ( 21587 )
            -- filter tempdb objects
    AND DatabaseID <> 2
            -- get only events in the past 24 hours
    AND StartTime > DATEADD(HH, -24, GETDATE())
   ORDERBY t.StartTime DESC ;


Change in microsoft documentation I am confused after microsoft changed the documentation style.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups

Earlier we used to be able to change the version of sql server at the top of the page.  Now I just see this.

How do I change the version of document to SQL Server 2014?

From the same documentation:

  Beginning in SQL Server 2014, readable secondary replicas can remain
  online even when the primary replica is offline due to user action or
  a failure. However, read-only routing does not work in this situation
  because the availability group listener is offline as well. Clients
  must connect directly to the read-only secondary replicas for
  read-only workloads.

So, you can go to 2014 because the article exists only for 2016.


Fill Factor Setting and Performance From what I've read, typically for indexing the recommendation is to leave the fill factor at 70-80, and the logic behind this seems that new data may be inserted into a table between existing values thus fill factor shouldn't be set as 100.  In a hypothetical example of usernames, new names may be added to the name field, so a fill factor of 100 could create a problem because, even though the pages are full, the new data won't be organized appropriately with the other data.

My question involves a table where data are organized by date (clustered index) and ID (non clustered index); each order has a date and an ID thus no "new" orders can occur between existing orders and no new IDs can appear between existing IDs - these happen sequentially.  For a table like this, would it be appropriate from a performance perspective to set the fill factor to 100 and reindex at 100, meaning are there performance advantages to having the pages full since no new data will appear between existing data?

The default fill factor is zero, which is identical to 100. As per the product documentation,

  The fill-factor value is a percentage from 1 to 100, and the
  server-wide default is 0 which means that the leaf-level pages are
  filled to capacity.

Trying to find an opitmal value for fill factor is a well-researched problem. In the described scenario, there is not much to be done unless the table doesn't use default values.


Which database engines will allow me to GRANT/REVOKE on a specific column? If I have a table with a single column of sensitive data, and I want to grant broad use of the table without exposing that one column, I know that I can create a VIEW that gives them access to all the non-sensitive columns. However, PostgreSQL allows you to grant column-level permissions in the form of

grant select (col1, ...coln) on table to role;

Are there other engines which provide this capability?

SQL Server 2000, 2005, 2008 has this capability

GRANT { ALL [ PRIVILEGES ] }
       | permission [ ( column [ ,...n ] ) ] [ ,...n ]
       [ ON [ class :: ] securable ] TO principal [ ,...n ]
       [ WITH GRANT OPTION ] [ AS principal ]

ADDED

In SQL Server 2005, 2008 it is possible to encrypt a column of data by using symmetric encryption, see B.Symmetric encryption that includes an authenticator. This variant returns a NULL value, just as if the wrong key was used.


sp_msforeach db - Why do we need to use the USE keyword sp_msforeachdb is an undocumented sp which is designed to run some T-SQL against every database in the server instance. Why then, does it appear that I need to use the USE keyword to do that

EXEC sp_MSForEachDb @command1 = 'SELECT DB_NAME()'

Prints the database name the sp_MSForEachDb command was run against n times, where n is the number of databases on the instance.

EXEC sp_MSForEachDb @command1 = 'USE ?; SELECT DB_NAME()'

Prints the name of each database.

Why is it necessary to use the USE statement? Shouldn't this behavior be inherent in the procedure?

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;';
-- 3,380 total rows on my system

You will also get a number of resultsets that all show the objects from foodb. You have to issue the command this way in order to get the command to execute in the context of each individual database:

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;';
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

One reason it works this way is that you might be executing from the context of the current database because it has a static object you want to use in relation to all of the databases. So, imagine you are in foodb and you create this table:

CREATE TABLE dbo.ObjectNameBlacklist
(
  name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name
  FROM dbo.ObjectNameBlacklist AS onb
  WHERE EXISTS
  (
    SELECT 1 FROM ?.sys.objects WHERE name = onb.name
  );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.