Speeding up insert in SQL Server I have a stored procedure which inserts some records in a few  tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins for this procedure to complete. Also same procedure can be called from multiple user sessions which results in some sessions waiting for 20 mins to get a response. Is there something I can do to reduce this time?

The database recovery mode is FULL(cannot be changed) and hence per my understanding sqlBulkCopy wont help here. Would love to hear your thoughts on this.

The table contains 15 columns. Among which  5 columns are foreign key to this table. There is no identity column but clustered index in combination of all the 5 foreign key columns. I have couple of index on other key columns . The rest of columns are decimal and varchar(50). I do have one varchar(max) column though.

Trying to get query or query same for your reference . A screen grab of most expensive operation(54) in the query:

If the clustered index of the table is on combination of columns instead of an identity column , will the inserts be impacted?

Basically the query is 'insert into this_table select from this table join with 5 other tables whose primary keys are foreign keys here to update those values. Joins are all on keys between the tables. I wouldn't mind posting the query but would you need the schema all tables involved in query as well?

EDIT2:
First of all thanks all of you guys for your answers,comments and thoughts. The object from which read happens turned out to be a heap with no clustered index. Modifying it did give significant improvement in read operation which improved overall .

I marking David Spillet answer as accepted as it gave a methodical way to approach the problem. Learned a few things about posting questions :)

Also thanks for Frisbee for comments and answers. I know i kept on modifying the question :)

Without more detail about the procedure[1] and the table it is inserting into we can't offer any specific help. Some general advice though:

If you are inserting the rows individually, try to arrange them so that they are inserted in large blocks, all at once if possible.
Confirm that the procedure is in fact being slow due to its own activity rather than waiting for locks held by other processes to clear.
If you are pulling the inserted rows from a view or complex inline statement make sure that this is optimised (perhaps there is something like an avoidable table scan happening when reading what it needs before it even tries to write the new rows?).
If you are using a complex statement and/or processing data in steps, check that you are not spooling stuff to tempdb unnecessary.

[1] if it is "top secret" so you can't just include the code directly[2] or (if it is long) via pastebin or similar then perhaps a sanitised version?
[2] though if it is that top secret maybe you should be paying a consultant rather than asking for free advice in public ;-)

Update

The query plan posted does suggest that for that particular statement you have a read issue not a write issue. It look like you have a correlated sub-query that is providing an aggregated figure per row and there isn't a suitable index to filter on (though there is one that allows it to avoid an extra sort operation, hence the index scan was not followed by a sort). More than that (which is educated guesswork anyway) I can't say without the details already requested. For this specific statement: the SQL for statement 54 and the table+index structure of "object8" and "object9", preferably without obfuscation[3].

[3] By disguising names you hide something that might give us clues about what you are actually trying to achieve. Help us help you, provide the details requested and don't actively hide details unless you really have to.

Regarding Cost Percentages

As Aaron said, the cost percentages are based on approximate times for a particular configuration of developer PC at Microsoft in the late 90s. Even then they were not intended to be anything other than estimates/indicators and IIRC the calculations have not been jiggled to account for changes in CPU and memory technology since. Also the calculation works on logical operations: they don't differentiate between work performed in memory and physical IO operations. Be careful too to consider the rest of the query and not just that one expensive chunk - it may be that the expense is caused by a missed optimisation elsewhere that results in an otherwise efficient operation to be run millions of times instead of a few or just one - the cost % may lead you to try to optimise that part when in fact the core problem is a step or a few away.

15K in 5 minutes 50 / second - that is reasonable

The likely culprit is page splits and index fragmentation

Give this a try
On the insert sort by the order of the PK on the insert
Put a fill factor of like 50 on all the indexes
Try an insert
If this work then tune the fill factor and add in index maintenance
Maybe even tune the order of PK to put those with a more common insert sort early - like if one FK is a customerID and you load by customerID then have it early
If it does not work then go back to your prior fill factors

You might also try tablock holdlock
Some times it is faster just to take a tablock get your business done and get out

If you have lock contention you can create a test table or test when usage is low


Efficiently remove 90% of a table's data I am working on a process to remove 90% of a table's data because only 10% is needed for testing.

The best way I have found includes storing 10% of the table's rows into a temporary table.

Current method

SELECT TOP 10 PERCENT *
INTO #temp_some_table
FROM some_table (nolock)
ORDER BY some_column DESC

TRUNCATE TABLE some_table

INSERT INTO some_table
SELECT *
FROM #temp_some_table

DROP TABLE #temp_some_table

This method is filling up tempdb and causing the disk to fill up as well.

Questions

Is there a more efficient way to delete 90% of a table's data
ex (DELETE TOP 90 PERCENT FROM sometable)

or

Is there a way to insert 10% of some_table's data into a temporary table using batches? Something like this:

DECLARE @r INT;

WHILE @r > 0
BEGIN

BEGIN TRANSACTION;

INSERT INTO [dbo].[##temp_cds_Basket]
SELECT TOP 10 PERCENT *
FROM [dbo].[cds_basket] s

SET @r = @@ROWCOUNT;
print @r

COMMIT TRANSACTION

END

Possible solution

How about this?

SET NOCOUNT ON;

DECLARE @r INT;
DECLARE @TenPercentDate datetime
with cte (some_column) as (

    select top 10 percent some_column from some_table (nolock) order by some_column desc
)
select @TenPercentDate = min(some_column)
from cte

select @TenPercentDate

 SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

 DELETE TOP (10000) from
  some_table
  WHERE some_column < @TenPercentDate

  SET @r = @@ROWCOUNT;
  print @r

  COMMIT TRANSACTION;

  --CHECKPOINT;    -- if simple

END

--rollback

I have three different ideas that I mentioned in a comment above. Here is a little elaboration on at least one of them (which you are stuck on due to self-diagnosed tunnel vision).

  Well, you could calculate the number of rows that make up 10% beforehand, and then compare that in your batch.

I was thinking about this but we don't really need to do the compare - we can just figure out what datetime value is at the edge of what we want to keep, and delete chunks of older rows until there are none left. For example:

SET NOCOUNT ON;

DECLARE
  @rc INT = 1,
  @cutoff DATETIME,
  @batchsize INT = 10000;

;WITH x(dt) AS
(
 SELECT TOP (10) PERCENT datetime_column
  FROM dbo.mytable
  ORDER BY datetime_column DESC
)
SELECT TOP (1) @cutoff = dt -- earliest row we want to keep
 FROM x
 ORDER BY dt;

WHILE @rc > 0
BEGIN
  DELETE TOP (@batchsize) dbo.mytable
    WHERE datetime_column < @cutoff
  SET @rc = @@ROWCOUNT;
END

You can see this post for some additional ways to enhance this.

  You could consider drop / select into instead of truncate / insert, and also think about using bulk-logged recovery during this operation.

I think this would be a valid option and should cause less pain than the above.

  You could also just perform one data movement operation:

SELECT TOP 10 PERCENT cols
  INTO dbo.newtable
  FROM dbo.oldtable
  ORDER BY datetime_column;

DROP TABLE dbo.oldtable;

EXEC sys.sp_rename N'dbo.newtable', N'dbo.oldtable', N'OBJECT';

This one is also less painful, but feel free to inject some debugging in there to be sure that you got the data you wanted before you perform the drop.

Note that in both of these latter cases there may be constraints, schema-bound views, etc. that prevent you from just dropping the table (obviously an inbound foreign key is not an issue because you are able to truncate, but outbound may still need to be dealt with). There will also be ramifications to existing plans (well deleting 90% of the data should also cause stats to update and invalidate plans, so really no different), and as @Kenneth points out below, you will need to re-establish permissions on the new table (so you might want to be sure you can script those in advance).

Delete
From Some_Table
Where Some_Column In
    (Select Top 90 Percent Some_Column
    From Some_Table
    Order by Some_Column)

Unless I'm missing something (and I could be!), this does the trick.


Should I snowflake or duplicate it across my facts? I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas.
In my warehouse, I've got two different fact tables that tracking daily changes in dollar values. Each entity in these fact tables have an underlying Sales Order and Line to which they relate. These SOs and Lines then have other related dimensions, such as customer, product, etc. About 12 sub-dimensions total so far.

My question is if I should be rolling all these sub dimensions up directly into the fact tables, or if I should use a little snowflaking in my warehouse, and have them branching off the Sales Order and Lines dimension instead.

The first option obviously follows a star-schema model better. However, if changes are made such as adding additional dimensions, it becomes more maintenance, basically having to do the ETL twice for each fact table, rather than just the once on the SO dimension. As well, if a new fact is added that relates to Sales Orders, I'd have to go through the whole process again.

As this is my first DW/OLAP project, I'm not familiar on where the line should be drawn on snowflaking, and other people's thoughts would be highly appreciated.

The "header - detail" pattern is very common the domain of sales transactions.
To answer your question, there are so many factors that will come into play which you've not discussed. For example:

If your DW infrastructure has a great deal of RAM and is on SSD storage, reads in this case are cheap, so it might make sense to denormalize some dimensions in the interest of usability.
What are the use-cases of the data?  In this case I can probably make assumptions - it is sales data. It'll be used for accounting, executive reporting, predictive analysis, customer service, and for just about every possible ad-hoc query you can imagine.

One general principle I use when deciding whether to snowflake a dimension or simply include it's value in the fact table is this:

If the dimension has many attributes which might be useful for reporting (or if there will be report(s) solely on that dimension), I create a dimension for it.

Example: Consider the CUSTOMER dimension. A sales order has a customer, but there are other attributes which belong with the CUSTOMER dimension which you might want to report on, like customer location, customer age/sex/marital status, customer type, customer create date, etc., and many other customer-related attributes. I wouldn't put all of these in a fact table, so in this case I "snowflake" to a customer dimension as there are many more attributes related to CUSTOMER which might be relevant to your sales fact data. There would likely also be reports that solely rely on the CUSTOMER dimension - like a "new customer by month" report. You wouldn't expect this to be in the fact data. The PRODUCT dimension is another I would almost always put in it's own dimension.

If the dimension is a single value with no other useful attributes connected to it, I may consider it for inclusion in a fact table.

Example: We might have an attribute called "Order Source Channel" - which might be a single value describing where the sales order came from e.g. it might have values like eCommerce, Kiosk, Point-of-Sale, Phone-In, etc. It's a single value, and no other related attributes exist for this entity. In this case, I am tempted to leave it in the fact table, rather than create a single-attribute dimension and require my users to do an additional join, etc.

Remember:

The above is a generality, and I don't treat is as a hard-fast rule
Data modeling is as much an art as it is a science. There are many scenarios which can go either way, and only experience will help you decide which way to go.
Usability of your DWH structure should be as much a consideration as is performance. I try to never create a data model that requires my power-users to write SQL queries with 15+ or more joins just to get at sales data. This will lead to someone writing incorrect SQL (it always does). This will sometimes be mistaken as "bad data". This is what you, as a DWH developer, don't want happening.

The reason you can't see "where the line should be drawn" is that very notion of "snowflaking" is bankrupt (no matter how much it's been written about).

The simplest approach is the best: get your design in BCNF and start using it.  Add constraints for correctness, views for convenience, procedures for sanity, and indexes for speed.  Do not assume performance will be a problem, or that joins cause performance problems.  Wait until they do -- if they do -- and address them then.  At most, you'll need to materialize that nice view or possibly write it overnight to a secondary table.

There's a huge sticky myth out there that there's something special about data warehouses.  There is not.  If your tables are normalized, you may be surprised how well the server "rolls up your dimensions", which is known everywhere else as "executes your query".


Mysql replication - Error No. 1146&1062 short version
In master master replication, How to have one master write data with odd IDs and the other one with even IDs? Is it the way to avoid conflict?
detailed version
I wanted to setup a MySQL master-master replication on two virtual machine I have on my system, to just play around with replication and get familiar with the concept.
I edited [mysqld] section of /etc/my.cnf of Master-1 and it looks like:
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir         = /var/lib/mysql

log-bin         = mysql-bin
binlog-ignore_db= mysql
binlog_format   = mixed

server-id       = 1

my.cnf of the Master-2 is almost identical to Master-2 except server-id which I set to 2. Then I restart the mysql service, and create slave users in each VMs:
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<the-other-VM-ip>'
IDENTIFIED BY 'somePass'

Followed to that I set the Master config in each VM:
CHANGE MASTER TO MASTER_HOST='<the-other-VM-ip>',
MASTER_USER='<replicator>',
MASTER_PASSWORD='somePass';

And finally start slave on both machines. I created a database in Master-1 and added a table with some value. When I commit and check in Master-2 I can see all the data. But When I do the same in Master-2, the changes are not committed to the Master-1. SHOW SLAVE STATUS \G in Master-2 show me the following error:
Last_Errno: 1146
Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist'
            on query. Default database: 'mydb'.
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
       SET thread.views = thread.views + aggregate.views WHERE thread.threadid = aggregate.threadid'

I stopped the salve, wrote SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; in mysql and then start the slave and ask for the status, which I get a new error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table mysql.user;
                Duplicate entry 'localhost-root' for key 'PRIMARY',
                Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
                the event's master log mysql-bin.000001, end_log_pos 26231
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

I believe this could be solve using auto-increment-offset = 2 in one config file, although I'm not sure about the procedure... beside not quite sure what SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; is and what it does, when Googled my first problem found it here.
I would be thankful for a description of the errors and how to overcome them.

I did as described here and it solved my problem.
So basically I added the following to [mysqld] section in my.cnf files:

Master-1:

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

Master-2:

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

Example, you have two DBs named DB_A and DB_B.

1/Master Slave Replication:
You have a server SRV_A which is master with DB_A and DB_B and slave SRV_B with DB_A and DB_B, all modification made on master (DB_A or DB_B) will be replicate on slave. You can't modify data on slave : SINGLE DIRECTION

2/Master Master Replication:
You have a server MASTER_A with DB_A and DB_B and MASTER_B with DB_A and DB_B.

MASTER_A and MASTER_B could'nt be master for the same databases so for instance you could have MASTER_A is master for DB_A and MASTER_B is master for DB_B.

All modifications made on MASTER_A on DB_A will replicate to MASTER_B and all modictions made on MASTER_B on DB_B will replicate to MASTER_A.

Real MASTER/MASTER concept is much more reserved for NoSQL DB...


Upgrade SQL Server 2016 R Service (in-database) I am just wondering whether this is doable. I have installed R service (in-database) together with SQL Server 2016 (patched at SP1+ CU1).

I notice R service version is at 3.2.2, which you can run the following script to check

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.2.2

But I also installed Microsoft R client, and notice its R service engine is versioned at: 3.3.2.

So my question is "does SQL Server R service (in-database) now support R version 3.3.2 ?" if so, how can I upgrade it? if not, I guess I will wait until MS ships the update.

I read MSDN, and in it, it mentions using sqlBindR.exe to do the update, but this tool is available only with Windows R server, which I did not install and I even doubt that if I installed it, whether it would update the in-database R service.

I faced the very same problem (when trying to follow the steps described in Use sqlBindR.exe to Upgrade an Instance of R Services). I could not find any SQLBindR.exe.

A friend of mine pointed out to me, that I had to explicitly install "Microsoft R Server", not as part of the SQL Server install bits and pieces, but as a separate download.

All options about how you can download Microsoft R Server are described in a blog post at MSDN, Run Microsoft R Server for Windows by Heidi Steen.

Download and install was done in some minutes. SQLBindR took some more minutes.
Voilá: Now the following script is showing me, what I was looking for. :-)

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.3.2

I agree with the answer by MEhrenmueller, but from my understanding the binding service comes with R-server, which means the expensive Enterprise license. Further than that, when I "unbinded" my R in-database, my R in-database folder WAS COMPLETELY ERASED. I had to re-install SQL Server.

I was expecting R in-database to be updated with SP1 or CU, but actually this seems not to be true from my tests. Unfortunately I don't remember the source that told me this.

So actually from my tests (upgrade via SP or CU, or binding) I see no solution for the question at the moment.


query processor ran out of internal resources and could not produce a query plan This is showing up in the logs several times a night.  How do I find the query causing the issue?  SQL Server 2008 R2 Sp1.

Look for queries with very long IN lists, a large number of UNIONs, or a large number of nested subqueries. These are the most common causes of this particular error message in my experience.

Occasionally the issue can be resolved by applying a product update (service pack or cumulative update) or enabling a supported trace flag, but more often the fundamental issue is the unusual SQL generated by some tools or data abstraction layers. The latter will require application changes, unfortunately.

Enabling documented trace flags 4102, 4118, 4122 (or the covering 4199) may also avoid the issue you are seeing. Review the documentation to see if they address the root cause in your case:

Microsoft Knowledge Base article for TF 4122
Microsoft Knowledge Base article for TF 4102, 4118
Microsoft Knowledge Base article for TF 4199

Possible it is not one query causing the issue.  If you are using a ton of ad-hoc queries it would be prudent to enable 'optimize for ad-hoc workloads'.  This way SQL Server will only create plans the second time a query is executed.

You can check using below SQL (Reference here):

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go


restore could not start database Restore failed for Server 'servername'.  (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: RESTORE could not start database 'ECP'. (Microsoft.SqlServer.Smo)

The database is created, but is not accessible using Object Explorer:

  The database ECP is not accessible. (ObjectExplorer)

Thanks for any advice on how I can make the database work.

EDIT: This query

select state_desc from sys.databases where name = 'ECP';

returns RECOVERY_PENDING.

I deleted the database again and had a new try with

restore verifyonly from disk = 'path\file';

This returns

  Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
  The path specified by "F:\ECPDATA1\ECPDATA1.mdf" is not in a valid directory.
  Directory lookup for the file "F:\ECPDATA2\ECPDATA2.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15100).
  Directory lookup for the file "F:\ECPDATA3\ECPDATA3.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Directory lookup for the file "F:\ECPDATA4\ECPDATA4.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Directory lookup for the file "E:\ECPLOG1\ECPLOG1.ldf" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
  The backup set on file 1 is valid.

Since it says, that the backup set is valid, I just have to get the files specified, to make it work, right? But on this server I don't have a drive E: or F:. How can I adjust this in the backup file? Or is it possible (I'm not that familiar with windows-server-2008-r2) to have some kind of symlinks like on linux?

Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):

RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';

In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.

If somebody came to this question when looking for answer to failed attempt to restore SQL Server database to localdb (like I did) then it might be localdb limitation.

I used SQL Server Express and was able to do a restore.


Get the rank of a user in a score table I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score

So far so good. The question is: How do I get what's a users rank?
For example, I have a users Name or Id and want to get his rank, where all rows are ordinal ordered descending for the Score.

An Example

Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

In this very case, Assem's rank would be 3, because he got the 3rd highest score.

The query should return one row, which contains (only) the required Rank.

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

I have this, which gives the same results as the one with variables. It works with ties and it may be faster:

SELECT COUNT(*)+1 as rank
FROM
(SELECT score FROM scores ORDER BY score) AS sc
WHERE score <
(SELECT score FROM scores WHERE Name="Assem")

I didn't test it, but I'm using one that works perfect, which I adapted to this with the variables you were using here.


How to Export Table sql to Excel file in sql server 2014? I want to export tbl_category to excel file with code. I can export with the wizard but I want to create procedures to create excel file daily.
tbl_category : 2 column contain : [id] is int and [category] is nvarchar(max)

code:

INSERT INTO OPENROWSET

('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\template.xlsx;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]')
SELECT [id]
      ,[Category]
  FROM [dbo].[TBL_Category]

windows 7 32 bit and 64bit.
sql server2014.
ms office 2010.

error:

  Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

1

This may be a file permissions/uac issue.

To prove/disprove that, create a new folder and copy (not move) the excel file into it, then give the windows security group "Everyone" Full control on the folder and its files. Update your export query with the new file location and try running it again.

If that works (or if you at least get a different error), then bear in mind that wherever you want to have the export file in the end, the file will need read/write permission assigned for the user account your SQL Server service runs under. (Or the "Everyone" group, but that wouldn't be best security practice.)

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
   | 'provider_string' }
      , {   [ catalog. ] [ schema. ] object
       | 'query'
     }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [  ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
 ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , MAXERRORS = maximum_errors ]
   [ , ROWS_PER_BATCH = rows_per_batch ]


Composite primary key from multiple tables / multiple foreign keys I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this...

A great answer was given to building a composite key from multiple foreign keys on this question but I want to do this from multiple tables. I am using Oracle 11g.

foreign key (Name, BoughtFrom, TimeBought)
  references the_other_table_name (Name, BoughtFrom, TimeBought)

Imagine that the "references" line actually includes multiple tables. I'm not even sure that the above syntax will work with Oracle.

I have one table "Student" and one table "Parent". It is a many-to-many relationship. My association table is named "Relation" and I would like to make a composite primary key from "PAR_ID" and "STU_ID". Both are foreign keys.

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL,
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id)
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id)
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Foreign key it is a relationship between the two tables,Primary key is a not allow the duplicates and nulls.


SQL Server 2012 Job History Missing I have a SQL Server 2012 database with a number of Agent jobs.  The jobs run without a problem but the job history is always empty.  Why?

I also note that running a job manually (right click and "Start Job at Step...") will run the job but the window showing progress of the job never closes.  It is as if the job steps complete but the job itself never completes.

All tasks have been executed as a sysadmin so permissions should not be an issue.

Running the jobs via sp_start_job results in the jobs completing but still no job history.

I've done all the usual checking/changing of jobhistory_max_rows_per_job etc.

While this only addresses a small part of your question (and you may already be aware):

When you right-click on a job and choose "Start Job at step...", the window that opens does not close automatically when the job is finished. You have to manually close the window (which can be done before or after the job completes).

Once the "Execute job" line in the window has a status (whether success or error), there should (normally) be an entry in sysjobhistory, even if the window hasn't closed (just confirmed in my system).

I've got the same problem. Since June 16th 2018 there are no protocol entries for my agent jobs anymore. I checked already with your script and it looks fine for me.
So, I have no idea what can be the reason. It might be there is a mistake in the SQL server itself, but how can I detect it. I have created a test agent job with an simple SQL statement, also here no protocol. When I check the error protocols then the protocal says that it was written in db sysjobhistory, but nothing happened.
So, has someone an idea to solve the problem?
Thanks a lot.
Cheers Klaus


computed column making the select query to run slow We are creating indexes, as per the requirements, on one server and copying all the indexes to the other servers. All servers have similar objects & schema but different resource configuration values.

We have an issue where a query that joins 4 views is completing on one server but the same query is taking 4 minutes on the other server. If I take out the computed column from the select list it is completing in 4 seconds.

The two execution plans are quite different.

1)  Is this a good plan to copy the indexes blindly where SQL is choosing different execution plans?

2) I have also tried to create the index on the computed column on the base table, still no luck, any further suggestions? ( We can’t create an index on views for some reason.)

"Is this a good plan to copy the indexes." Indexes exist to improve the response time of read operations. Those operations arise from the workload. If the workload differs, the indexes should differ, too.

For example, it is not uncommon to have one server handle the OLTP activity and replicate the data to a second server where analytic queries are processed. The first server's work will be mostly single row lookups. The second's range scans and aggregates. In this case having different indexes on each server makes good sense. If all indexes were on both servers there would be additional, unnecessary work to do during writes and maintenance.

I would try persisting the computed column. If you do this the cost of computing the column will be incurred on writes, persisted, and then it won't slow down reads.


Unable to remove a user in Oracle 11g I have a streams replication  related user in Oracle 11g. I see the user in dba_users table and was anle to login using the user but when I try to drop the user i get the error.

SQL> drop user <username> cascade;
Error at line 1:
ORA-1403: no data found.

Loos like data got corrupted in internal tables.

Any idea how to fix this mess and remove the user?

The ORA-01403 seems to indicate that you have a System Event Trigger for DROP USER.

You'll need to hunt down the appropriate trigger.

SELECT *
from dba_triggers
where base_object_type like 'DATABASE%'
and triggering_event like 'DROP%'
and status='ENABLED'
;

If that doesn't work, then I recommend that you open a ticket with Oracle Support.

Check te user exist:

select * from all_users

or

select * from dba_users;

and see if exist or no.


2 doubts in db design I'm studying about db by doing some modeling exercises. But I've two doubts in a exercise.

In the exercise:

  A Event can have only one organizer, and the organizer is a user, and a user can organize and participate in many events.

So this is a one to many relationship between organizer and event. My doubt is about the need to create a third table for the relation, because it's just necessary to have a third table in a many to many relationship, right?

But for example, in this case the event organizer has some attributes that don't belong to the user like "details", so  we need to create a third table for the event organizer, but this table is not a relationship table it's just a normal entity table right?

But to do the relation, that a user, that is an organizer can organize multiple events and an event is organized by a single organizer, that is a user, how should we do this? Is the below correct? Because the "event_organizer" table seems a relationship table but maybe it should be a entity table.

The other doubt is that an event can have a maximum of 3 categories, and one category can have many events. So it is a many to many relationship? Or because an event can have a maximum of 3 categories is not and it needed a different approach?

I'm not familiar with the layout of your database design (there are many layouts in the market). But what I'm figuring out is that you are trying to connect two entities together, directly. Which is wrong. Two entities can only be connected by a relationship (no matter what ER layout you are using).

Your first query can be subdivided into three parts:
1) An event can have only one organizer
2) Organizer is an instance of User
3) There is a one to many relationship between event and organizer.

So you have three entities: Event, Organizer, User.
Then, if you have studied about weak entities, you would want to convince yourself that organizer is a weak entity which is dependent on the entity User. Connect entities User, and Organizer with a relationship "is_instance". Now you are done with the entity User.

Let's now move on to the relationship between entities Organizer, and Event. You already figured out that it has a one to many relationship from Event to Organizer. So great!!

Now coming to your next query: An event can have at most three categories but a category can have any number of events. You can say that this is a kind of many to many relationship. Some ERD allow you to explicitly specify the cardinality between the entity and the relationship (as far as I remember it is done by specifying the number over the arrow joining the entity and relationship). So you can model this as joining the entities Event, and Category by a relationship has_category and specify the range 1..3 over the line joining Event and has_category.

Extra clarification:
There is always a many to one relationship from a weak entity towards its underlying entity. Your inclusion of details to the entity organizer and the dependence of Organizer's ID on User's ID makes it a separate entity.

(Note: due to lack of information, I assumed that there may be more than one value for the attribute "detail" for the same user in the entity Organizer. If this is not so and that there is only one tuple in the entity Organizer for every ID such that every tuple in the entity Organizer is unique on attribute Organizer_id, then you could also choose to make the Organizer entity as a strong entity and have a total participation of entity Organizer in the relationship between User and Organizer)

Additionally, your query also states that the user can participate in many events, so there can be a many to many relationship between entities User, and Events.

Hope this helps.


Stop never ending backup in SQL Server 2012 Express Our production database log file has grown to consume all disk space on our server.

I know it's bad practice but I want to be able to shrink it in order to restore the service as soon as possible.

Still, when I go and launch my SHRINKFILE command,I get this error :

  Msg 3023, Level 16, State 2, Line 2
  Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on
  a database must be serialized. Reissue the statement after the current
  backup or file manipulation operation is completed.

It seems to me that the backup will never end. Is there a way to stop it manually ?

(I tried the SQL Server Agent thing, as told in the MSDN documentation, but it is not present in my SQL Server Express)

Restarting SQL Service will kill backup process and then you can shrink. Below query will show you spid of backup process, if any running on server.

SELECT *
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'

Below query will kill backup process if you don't want to restart SQL service.

Declare @SPIDU Varchar(10), @Query Varchar(50);
SELECT top 1 @SPIDU = spid
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'
Select @Query = 'KILL ' + @SPIDU + ';'
Exec( @Query)

Make sure you do clear trans log by taking backup or else you wont be able to reclaim space from trans log file.


What is the most efficient method of performing the FIZZBUZZ test in SQL Server? This may not be completely on-topic, however it's a slow day here.

Is there a more efficient method of obtaining a list of numbers from 1 to 49 with a column containing the words FIZZ when the number can be evenly divided by 3, BUZZ when the number can be evenly divided by 5, and FIZZBUZZ when the number can be evenly divided by both 3 and 5?

My attempts are (CAUTION, this will empty your procedure cache, so DON'T RUN ON a PRODUCTION BOX):

DBCC FREEPROCCACHE
GO
/*VARIANT1*/
;WITH t AS (
    SELECT RowNum = ROW_NUMBER() OVER (ORDER BY o.object_id)
    FROM sys.objects o
)
SELECT t.RowNum
    , CASE WHEN ((t.RowNum % 3) + (t.RowNum % 5)) = 0  THEN 'FIZZBUZZ'
    ELSE
        CASE WHEN t.RowNum % 3 = 0 THEN 'FIZZ'
        ELSE
            CASE WHEN t.RowNum % 5 = 0 THEN 'BUZZ'
            ELSE ''
            END
        END
    END
FROM t
WHERE t.RowNum < 50;
GO 100

/*VARIANT2*/
DECLARE @t TABLE
(
    Num INT NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @t (Num)
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o;

SELECT t.Num
    , CASE WHEN ((t.Num % 3) + (t.Num % 5)) = 0  THEN 'FIZZBUZZ'
    ELSE
        CASE WHEN t.Num % 3 = 0 THEN 'FIZZ'
        ELSE
            CASE WHEN t.Num % 5 = 0 THEN 'BUZZ'
            ELSE ''
            END
        END
    END
FROM @t t
WHERE t.Num < 50;
GO 100

SELECT CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
    , MAX(deqs.execution_count)
    , SUM(deqs.total_worker_time)
    , AvgWorkerTime = SUM(deqs.total_worker_time) / MAX(deqs.execution_count)
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) dest
WHERE (dest.text LIKE '%/*VARIANT1*/%'
    OR dest.text LIKE '%/*VARIANT2*/%')
    AND dest.text NOT LIKE '%/*NOT_ME!*/%'
GROUP BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
ORDER BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
/*NOT_ME!*/;

As advised by @AaronBertrand, I've modified my attempts to run each set of statements 100 times each, then show the times recorded by SQL Server through sys.dm_exec_query_stats.

The results:

            Runs    total_time      average time
VARIANT1    100     42533           425
VARIANT2    100     138677          1386

Using a SQL Server 2014 memory optimized table and a natively compiled procedure:

-- Setup
CREATE DATABASE InMem;
GO
ALTER DATABASE InMem
ADD FILEGROUP FG1
CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE InMem
ADD FILE
(
    NAME = 'FN1',
    -- Change to suit your system
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\FN1.mod'
)
TO FILEGROUP FG1;
GO
USE InMem;
GO
CREATE TYPE dbo.FizzBuzzTableType AS TABLE
(
    n integer NOT NULL INDEX i,
    FizzBuzz varchar(8) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);
GO

Native procedure:

CREATE PROCEDURE dbo.FizzBuzz
WITH
    NATIVE_COMPILATION,
    SCHEMABINDING,
    EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'english'
)
    DECLARE @n AS dbo.FizzBuzzTableType;

    DECLARE @i integer = 1;
    WHILE @i < 50
    BEGIN
        IF @i % 15 = 0
        BEGIN
            INSERT @n (n, FizzBuzz)
            VALUES (@i, 'FizzBuzz')
        END
        ELSE
        BEGIN
            IF @i % 3 = 0
            BEGIN
                INSERT @n (n, FizzBuzz)
                VALUES (@i, 'Fizz')
            END
            ELSE
            BEGIN
                IF @i % 5 = 0
                BEGIN
                    INSERT @n (n, FizzBuzz)
                    VALUES (@i, 'Buzz')
                END
                ELSE
                BEGIN
                    INSERT @n (n, FizzBuzz)
                    VALUES (@i, CONVERT(varchar(8), @i));
                END;
            END;
        END;

        SET @i += 1;
    END;

    SELECT
        N.n,
        N.FizzBuzz
    FROM @n AS N
    ORDER BY
        N.n;
END;

Test:

SET NOCOUNT ON;
PRINT SYSUTCDATETIME();
GO
DECLARE @T AS dbo.FizzBuzzTableType;

INSERT @T (n, FizzBuzz)
EXECUTE dbo.FizzBuzz;
GO 100

PRINT SYSUTCDATETIME();

Typical results:

-- 95ms for 100 iterations, < 1ms each
2014-12-31 10:07:13.7993355
Beginning execution loop
Batch execution completed 100 times.
2014-12-31 10:07:13.8943409

This writes the procedure output to an in-memory table variable, because otherwise we're just testing the speed of displaying results in SSMS.

One million rows

The above native procedure takes about 12 seconds to run on 1,000,000 numbers. There are all sorts of faster ways to do the same thing in T-SQL. One I have written before follows. It runs in about 500ms on my laptop on a million rows when the intended parallel plan is achieved:

IF  OBJECT_ID(N'tempdb..#Result', N'U') IS NOT NULL
    DROP TABLE #Result;

IF  OBJECT_ID(N'tempdb..#Thousand', N'U') IS NOT NULL
    DROP TABLE #Thousand;

SET NOCOUNT ON;
DECLARE @start datetime2(7) = SYSUTCDATETIME();

CREATE TABLE #Thousand
(
    n integer NOT NULL,

    CONSTRAINT PK_#Thousand
    PRIMARY KEY CLUSTERED (n)
);

-- Add 1,000 rows numbered 0-999 to #Thousand
WITH
    L1 (n) AS
(
    SELECT  V.n
    FROM
    (
        VALUES  (0), (1), (2), (3), (4),
                (5), (6), (7), (8), (9)
    ) AS V (n)
),
    Thousand AS
(
    SELECT  n =
        CONVERT
        (
            integer,
            ROW_NUMBER() OVER (
            ORDER BY (SELECT NULL))
            - 1
        )
    FROM L1
    CROSS JOIN L1 AS L2
    CROSS JOIN L1 AS L3
)
INSERT #Thousand (n)
SELECT n
FROM Thousand;

-- To hold the Fizz Buzz output
CREATE TABLE #Result
(
    n integer NOT NULL,
    result varchar(8) NOT NULL
);

INSERT #Result
SELECT
    Million.n,
    Million.result
FROM
(
    -- Modulo operation to encourage few outer rows parallelism
    SELECT  n
    FROM    #Thousand
    WHERE   n % 1 = 0
) AS T1
-- Outer Apply to keep the Compute Scalar parallel
OUTER APPLY
(
    SELECT
        F2.n,
        F2.result
    FROM #Thousand AS T2
    CROSS APPLY
    (
        -- Row numbers 1 to 1,000,000
        SELECT  (T1.n * 1000) + T2.n + 1
    ) AS F1 (n)
    CROSS APPLY
    (
        -- The Fizz Buzz bit
        SELECT
            F1.n,
            result =
                CASE
                    WHEN F1.n % 15 = 0 THEN 'FizzBuzz'
                    WHEN F1.n % 3 = 0 THEN 'Buzz'
                    WHEN F1.n % 5 = 0 THEN 'Fizz'
                    ELSE CONVERT(varchar(8), F1.n)
                END
    ) AS F2
) AS Million
OPTION  (MAXDOP 4, QUERYTRACEON 9481);

PRINT DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME());

PostgreSQL

PostgreSQL provides generate_series, a Table-Value Function (Set-Returning Function) which makes this substantially simpler. I'm assuming you don't want anything output whatsoever when the number neither 3, nor 5 goes into it.

SELECT x, str
FROM generate_series(1,49) AS gs(x)
CROSS JOIN LATERAL (VALUES (CASE
  WHEN x % 15 =0 THEN 'Fizzbuzz'
  WHEN x % 3  =0 THEN 'Fizz'
  WHEN x % 5  =0 THEN 'Buzz'
END)) AS c(str)
WHERE str IS NOT NULL;


Tuning SQL Server Performance While profiling my application against SQL Server 2008 running against a JDBC Application most of the time consumed were by following

COMMIT TRAN
FETCH API_CURSOR & exec sp_cursorfetch

I cannot reduce the frequency of commits in my app - so is there any setting/configuration like increasing the redo log buffers or log file size that can provide relief ?

And should I look at avoiding server side cursors as it eats up most of the time?

But since most of those statements involve blobs probably batch returning it makes more sense.

Please advice.

Cursors may be the right solution to a given problem, but usually not in SQL Server.  Try to use set-based solutions instead of an iterative one if at all posible.  Worst case scenario, you can look at using an iterative SQL solution.  I've done that before and ended up an order of magnitude faster than an equivalent cursor implementation.

You can return BLOBs from the DB in normal SQL statements so you don't need a cursor for that - granted, if your application can't handle the incoming data stream, you might be changing cursor waits for async_network_io waits (meaning SQL is done, but your app is too slow - Access does this all the time).

If you post more details about what your app is doing or more specifics, we might be able to get more focused advice.

Yes, cursors are evil. Probably - most evil thing on the Earth 8-)

For proper helping - what recovery model you use (simple, full?)

If you sure, that the most of the time eats COMMITs - then try to add more big log files to the your DB AND to the tempdb.

And

try to shrink current log files to its minimum and immediately expand to relative big size in one step. It helps to create bigger virtual log files inside your log-files, which performs a little bit faster

And

adding more logs removes hot-spot of your IO from one log-file and spreads it across several.

Of course

log files should reside on faster disks. Depending on your load - may be more
preferable to place tempdb to dedicated fastest disks.


Do I really need to keep .LDF files? Each month we make an end of month snapshot of our production database. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each of these snapshots has an .MDF and .LDF file.

I want to delete the .LDF files and free up some space on the server. Are there any reasons why I need to keep the .LDF files?

Clarification:

Our production database is recreated each night from file extracts off of another system. We only report off of the production database... no updates are ever done.

Nightly Process:
From what I can tell...
Each night the databse tables are truncated
The tables are populated through a series of bulk insert statements
Indexes are rebuilt

You shouldn't delete the log file.  If you are trying to reattach a data file without the log, SQL Server can technically recreate it, but there are a few potential issues, like if there were open transactions when the database was detached.  In which case, you'd have total data loss.

Consume the space, and don't delete your log files.  You're asking for trouble with that.

See this article on Transaction Logs, in particular the "Log Mismanagement" portion.

Here is the solution I came up with to shrink the LDF files.

Detach Database
Rename LDF file to *_old.ldf
Attach Database
Remove the refernece to the missing LDF

This recreates an LDF file that is 504K in size.

Delete *_old.ldf
Empty recycle bin

This has reclaimed a significant amount of disk space on the server. It works for us because all of these databases are static reporting databases ONLY. No Inserts, Updates or Deletes will ever be performed against these databases.

UPDATE 2019-09-24: Yeah, I agree this is a really bad idea. I stopped doing this almost immediately. I have rebuilt all the indexes using a fill factor of 100. Shrunk only the .ldf files. And changed all the databases to be READ ONLY.


Restore msdb from SQL Server 2008 R2 to SQL Server 2012 instance I'm moving from a SQL Server 2008 R2 instance to a SQL Server 2012 instance on a separate box. I want to restore msdb to get all my agent jobs and the like and I'm getting the following error:

  Restore of database 'msdb' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)
  System.Data.SqlClient.SqlError: The backup of the system database on the device
  \10.0.0.3\BackUps\NOFAULT2010_msdb_FULL_20121016_220006.bak cannot be restored because
  it was created by a different version of the server (10.50.1600) than this server
  (11.00.2100).
  (Microsoft.SqlServer.SmoExtended)

I would understand this if I was trying to go from 2012 to 2008 but this is new to me.

Anyway to get this to work? Or if not best way to transfer the agent jobs, users, blazi blah ?

Thanks

If you were trying to restore a user database - this would restore alright. The problem is that this is a system database. The system databases are designed for the version of SQL they were intended to work in. In order for SQL Server 2008 R2 MSDB to work in SQL Server 2012, you'd have to have no features different between the two versions. Make sense?

The Short Answer
To bring your jobs across, you'd script them out from the old server and apply the script on the new one. You can do this as simply as right clicking and scripting each job if you have a few. Or looking into a script with PowerShell or some other approach if you have a ton and don't want to one at a time.

To take the Logins you'd use the Sp_help_revlogin script I reference below and a script to copy server level roles and permissions from the old and copy them to the new..

Basically for 90% of what you'd bring across, I'm pretty sure the answer is "script it out" and then just apply that script on the new server and the logins, the jobs, etc. will all now live in the Master and MSDB databases designed for SQL Server 2012.

The slightly longer answer and a quick discussion on approaches to migration/upgrade

So if you are trying to transfer all of these objects to SQL Server 2012 you have two basic options on your approach.

Do an in place upgrade. Upgrade your instance from SQL Server 2008 R2 to SQL Server 2012..

Pros - you get all that 'stuff' (agent jobs, linked servers, logins, alerts, operators, mail profile, etc.) and you don't have to copy objects around..

Cons - it can be a bit messy, it works fine nowadays and is supported but I am paranoid and like to know for sure that I have success and a quick rollback option (if issues on new server, just revert back to your old server during a migration. With an in place upgrade, it is much more, well, final).

You can start here for an in place upgrade.

Migrate to a new server...

In this case you just flip the pros and cons from above.. The approach isn't that tough or strenuous. It just requires some good planning. Basically you:

On your old server, script out all of the objects you want to move according to the instructions and approach for each object type which can typically be found in Books Online (objects like Jobs, Linked Servers, SQL Agent Alerts, etc.)...
Use a tool like sp_help_revlogin to move your logins across and a script kind of like this to move the login permissions across..
Backup and restore your databases which already contain the in-DB users and permissions(I like doing this over detach attach because it helps preserve that rollback ability but I've seen and done this either way).. Change your compatibility mode if you are planning on supporting the DBs in 2012 mode and have tested them that way. Or keep them in 2008 mode if that was your plan.
Run those scripts for all of the objects like jobs, logins, etc. on the new server that you created above from the old server(good to do most of these after the DBs.. as logins will error on you if their databases that they default to aren't there or a T-SQL step in a job's database isn't there, etc.)

The other nice thing about this approach is you can do a trial run ahead of time during business hours.. Point a test or dev version of the app(s) used on that instance to the 2012 and see what breaks.. Fix it and test out your checklist, your rollback plan, etc. and do that prep for go live night.

This is an old post, but I'd like to propose a different solution.

On your Sql2012 system, restore a backup of the SQL2008 MSDB database, but give it a different name during the restore ('msdb_2008'). Also, be sure not to overwrite the original files. The restore process will upgrade the database version to SQL2012.
Detach the MSDB_2008 database from your SQL2012 instance (but remember where the recovered files are!)
Stop SQL Server service (SQL Agent will then stop as a result)
Browse to the area where your original MSDB files are, and move them to a subfolder for safekeeping.
Copy the MSDB_2008 database files to the path there the original MSDB files were, renaming them in the process to match the original MSDB files.
Restart SQL Server.


Get Job id OR Job name from within executing job I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at that time, etc. The info is then exposed via SSRS so we can see an job failures / long running jobs / & jobs that haven't run but should have(or if someone messed with a schedule).

To do this, I have 2 job steps added into each job on every server and I would like to reduce the script down to just 1 step added to each job..possibly even call it from a network share..

But my issue is one of the 3 parameters I pass. I need to get the executing jobid or job name from within the executing job so I don't have to hardcode the name parameter.
The 3 parameters I pass are jobid, status(success/fail), errormsg. The powershell script I wrote is pretty straightforward.

Invoke-sqlcmd -ServerInstance "MYRemoteSYSTEM" -Database remoteDB -Query "exec dbo.JOB_LOG  'JOBNAME/ID','Success/FAIL','BAD MESSAGE HERE'"

This writes what I need to the table. I've looked at
msdb.dbo.sp_help_job /
msdb.dbo.sp_get_composite_job_info /
dbo.xp_sqlagent_enum_jobs /
but none of these will guarantee I get the ID or name of the correct executing job in the event that there are more than 1 jobs executing at the same time.

I've even tried looked at sys.sysprocesses but I think since the agent job is a powershell script, it shows as ".Net SqlClient Data Provider" so I can't trim the binary JOBID off the jobs that show as "SQLAgent - TSQL JobStep (Job 0xF1800243164745429C30474FFD5C990C : Step 1)"
---this I learned from Denny cherry's post - thanks denny-

Any ideas on how to grab the executing jobid would be greatly appreciated.

Thanks,

Chris

You will have to use tokens in your job steps to get your own job id. Details here: Using Tokens in Job Steps.

At the end of the article there's one example with jobid:

SELECT * FROM msdb.dbo.sysjobs
WHERE @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;

DECLARE @jobname sysname, @jobid uniqueidentifier

SELECT @jobname=b.name,@jobid=b.job_id
FROM sys.dm_exec_sessions a,msdb.dbo.sysjobs b
WHERE a.session_id=@@spid
AND
(SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)),1,10)) = SUBSTRING(a.PROGRAM_NAME,30,10)


Can low memory cause Table Spool operators in plan I was trying to optimize a query and found that the plan had table spool(eager) in it.  But when I ran it on another server which had much more memory(256GB compared to 64GB) than the other one the operator was gone and the query was much faster.

So I am wondering if that memory is even the cause of the plan change which I see ie is it even a plausible theory?

volume of Data is the same.

SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn’t, things can go terribly, terribly wrong.

MOre info

https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/


MongoDB 3.4 Sharding Error. “Cannot accept sharding commands if not started with --shardsvr” I have installed mongoDB 3.4.5 in centOS 7.2 machine. I am configuring sharding in standalone mode.

Steps I followed:

For Replica Sets:

mongod --replSet rs0 --dbpath /data/srd/rs0 --logpath /data/srd/rs0/log.rs0 --port 27000 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --dbpath /data/srd/rs1 --logpath /data/srd/rs1/log.rs1 --port 27001 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --dbpath /data/srd/rs2 --logpath /data/srd/rs2/log.rs2 --port 27002 --fork --logappend --smallfiles --oplogSize 50 &

For Shards:

mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd0 --logpath /data/srd/srd0/log.srd0 --port 27020 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd1 --logpath /data/srd/srd1/log.srd1 --port 27021 --fork --logappend --smallfiles --oplogSize 50 &
mongod --replSet rs0 --shardsvr --dbpath /data/srd/srd2 --logpath /data/srd/srd2/log.srd2 --port 27022 --fork --logappend --smallfiles --oplogSize 50 &

For Config server:

mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep0 --logpath /data/srd/cfgrep0/log.cfgrep0 --port 27006 --fork &
mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep1 --logpath /data/srd/cfgrep1/log.cfgrep1 --port 27007 --fork &
mongod --configsvr --replSet configReplSet --dbpath /data/srd/cfgrep2 --logpath /data/srd/cfgrep2/log.cfgrep2 --port 27008 --fork &

To start Mongos:

mongos --configdb configReplSet/sit:27006,sit:27007,sit:27008 --port 26060

I am able to login the mongos shell and also able to add the shards. But while enabling the shard I am getting exceptions as below.

mongos> sh.enableSharding("test");
{
        "code" : 193,
        "ok" : 0,
        "errmsg" : "Cannot accept sharding commands if not started with --shardsvr"
}

First.. You cannot have two same name replica sets in one cluster (rs0), rename that second one to rs1. (Ports 27020 - 270122).

Error message is telling directly what you should do.. So, add --shardsvr to every started replica set.

From mongod --replSet xxx  to  mongod --shardsvr --replSet xxx

And of course after log in to mongoS service, you need to add those (two) replica sets to your cluster

sh.addShard("rs0/address1:27000, address2:27001, address3:27002")

And that other rs1 too..

In my case I had no space left in my partition /var/log.

Use df -h to check free space in your partitions and delete the bigger logs.


databases cross references baselines We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. Creating the baselines is not an problem. But after baseline creation, when trying to deploy on other environments, say a local developer instance or staging or alike, this cross reference issue is causing us several headaches. After doing some research and checking on possible approaches I still can't find a reliable way of managing this issues.

One approach proposed is to split baseline creation into "modules" that could be run individually so the cross referenced objects remain in one single script and be run alone. But what if the referenced objects on the other database has changed, then how I can track those changes? Maybe an index was added on referenced table and directly affects performance on the current sp? Or for whatever reason the referenced table changed column name, even deleted that column that is referenced and added a different one? What a nightmare.

So, question is, is there a recommended solution for this issue? For solution I mean, methodology, step-by-step approach, or whatever else that helps getting close to a solution with the deploying issue when having cross reference between database objects.

And for the moment, removing the database cross references is a no go. Way to much application dependent stuff to be corrected/updated and can't be done for the moment.

Not completely sure if this question follows the standards, as it can maybe trigger some opinion based answers. But I don't have more information to throw in for the moment. If you feel it should be flagged, then go.

I know cross references between schemas/databases are commonly used in Oracle and now in MS-SQL as well :)

DBmaestro TeamWork (where I work) provides database enforced change management which consists of:

Database enforced version control – enforcing the version control process on the database objects.

Database baseline impact analysis – generating delta script (between environments) utilizing version control labels

and it does support cross references between schemas (Oracle) and databases (MS-SQL).

For more information on database enforced change management you can read our whitepaper


ORA-01422 Error Occurred When I Call Procedure with Number Parameter I want to write PL/SQL Procedure that updates salary which is less than 2000. I wrote this procedure.And when i call it with integer id 'ORA-01422: exact fetch returns more than requested number of rows' error is thrown by TOAD. My procedure is like below:

DECLARE
PROCEDURE update_salary(ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   IF (c_sal <= 2000) THEN
      UPDATE customers
      SET salary =  salary + 1000
      WHERE id = ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id=ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/

I print parameter id with dbms_output. The parameter is coming correctly. How can i fix this error !

Naming your parameter the same way you named your column is quite unfortunate.

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = ID;

In the above statement, typing id in lowercase or ID in uppercase makes no difference, it is case insensitive. Just because you named your PL/SQL parameter ID (in uppercase), does not mean the database will use it when you type ID (in uppercase) in the above. Even if the id in customers has unique values, the above SELECT returns all* rows from the table, because the id column precedes the ID parameter, and id always* equals ID (*except for NULLs).

You could just simply use a different name for your parameter, for example I usually use the p_ prefix for them, so your ID parameter could be changed to P_ID:

DECLARE
PROCEDURE update_salary(P_ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = P_ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   SELECT  salary
   INTO  c_sal
   FROM customers
   WHERE id = P_ID;
   IF (c_sal <= 2000) THEN
      UPDATE customers
      SET salary =  salary + 1000
      WHERE id = P_ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id=P_ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/

This mean query:

SELECT salary
  INTO c_sal

return more than 1 row
check the table for count records with ID = 1

if ID by any legal reason non unique - need make LOOP


Why does MySQL + Windows Server = crashed tables periodically? Windows Server 2008 R2 x64
MySQL Server 5.6.14 x64

Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by choice) and a half a dozen Linux servers running MySQL. For some reason, the tables on the Windows servers get marked "crashed" every so often. We never have these problems on our Linux (Ubuntu) servers.

2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\ffcont' is marked as crashed and should be repaired
2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\bldr' is marked as crashed and should be repaired
2014-01-23 10:11:43 1868 [ERROR] MySQL: Table '.\elite_prod\frtfwd' is marked as crashed and should be repaired
2014-01-23 10:11:44 1868 [ERROR] MySQL: Table '.\elite_prod\histry' is marked as crashed and should be repaired

It seems we have to deal with this issue every couple of weeks or so. Very frustrating.

From the error message I can quickly tell the following:

The database is elite_prod
ffcount,bldr,frtfwd, and histry are all MyISAM tables

Because you are using the MyISAM Storage Engine, you have two major handicaps:

HANDICAP #1

Changes to MyISAM are cached differently (regardless of the OS)

The index pages for MyISAM tables are cached in the MyISAM Key Buffer (sized by key_buffer_size)
Index changes are flushed to the .MYI by the MyISAM Storage Engine
Data are never cached as there is no engine-centric buffering for data
Data changes are flushed to the .MYD by the OS (OUCH !!!!)
I discussed this before

What are the main differences between InnoDB and MyISAM?
Few Insert queries rollbacked due to system crash
My database keeps crashing on startup how can i repair it without it running?

HANDICAP #2

Microsoft Windows is terrible at caching disk changes. Even running FLUSH TABLES; in MySQL in a Windows environment is not a panacea. IMHO anyone using PostgreSQL or Oracle should be able to voice this same complaint about Windows. I will leave it to the SQL Server gurus to answer how they find Windows caching in terms of SQL Server.

ANALYSIS

MyISAM maintains a count of open file handles against the tables.

If the mysqld process or the Windows Server crashes, every MyISAM that had open file handles will retain the open file handle count internally.

When you access a MyISAM table for the first time since mysqld was started, it should have a zero file handle. Otherwise, you get that error message marked as crashed and should be repaired. This explains the tables periodically coming up as crashed crashed.

See my post MyISAM table keeps crashing. What are my options?

SUGGESTIONS

You could switch those tables to InnoDB and let the InnoDB Buffer cache everything, or at least better caching. I would still worry about Windows in this respect because the option innodb_flush_method is disabled in the Windows version of MySQL. I say disabled because the MySQL Documentation says:

  Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

If you want leave the tables as MyISAM, please go back to Ubuntu because it is a bit more diligent when it comes to flushing disk changes, especially when it has enough RAM. Even a terabyte of RAM will not do a thing for MyISAM tables in Windows.

why don't you repair them

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

And after if error continues then troubleshoot !

Make sure nothing is "touching" the data directory externally, such
as virus checkers
Shutdown MySQL properly
Use --myisam-recover option to automatically check/repair your tables
in the event that shutdown wasn't done properly
Or you can convert to all myisam tables to InnoDB(as InnoDB has a less chance of corruption)


How to safely change MySQL innodb variable 'innodb_log_file_size'? So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:

mysql> show variables like 'innodb_buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

When I went to change the innodb_log_file_size value (example my.cnf on mysql's innodb configuration page comments to change the log file size to 25% of the buffer size. So now my my.cnf looks like this:

# innodb
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M

When I restart the server, I get this error:

  110216  9:48:41  InnoDB: Initializing buffer pool, size = 128.0M
  110216  9:48:41  InnoDB: Completed initialization of buffer pool
  InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
  InnoDB: than specified in the .cnf file 0 33554432 bytes!
  110216  9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
  110216  9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

So my question: Is it safe to delete the old log_files, or is there another method to change the innodb_log_file_size variable?

Yes it is safe to delete the log file once mysqld has been shutdown

In light of this, just perform the following steps:

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

Starting up mysqld will recreate ib_logfile0 and ib_logfile1

Give it a Try !!!

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct = 0

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.

chown mysql:mysql -R /etc/mysql /var/lib/mysql && cd /var/lib/mysql && rm -f ib_logfile* && service mysql restart || service mysql restart

Try it, guaranteed to be working [tested on Debian 6]


MySQL 5.5.57 No longer supports myisamck *.MYI I use
    myisamchk *.MYI
to optimise all my tables.

However this no longer works with the MYI extension

# myisamchk mytable
Checking MyISAM file: mytable
Data records:   17952   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1

# myisamchk mytable.MYI
myisamchk: error: 140 when opening MyISAM-table 'mytable.MYI'

Still happening in MySQL 8.0.11

Walkarround solution: NOT USE THE MYI EXTENSION

myisamchk mytable


Why does ANSI SQL define SUM(no rows) as NULL? The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets:

COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = NULL
SUM(...) = NULL

Returning NULL for AVG, MIN and MAX makes perfect sense, since the average, minimum and maximum of an empty set is undefined.

The last one, however, bothers me: Mathematically, the SUM of an empty set is well-defined: 0. Using 0, the neutral element of addition, as the base case makes everything consistent:

SUM({})        = 0    = 0
SUM({5})       = 5    = 0 + 5
SUM({5, 3})    = 8    = 0 + 5 + 3
SUM({5, NULL}) = NULL = 0 + 5 + NULL

Defining SUM({}) as null basically makes "no rows" a special case that does not fit in with the others:

SUM({})     = NULL  = NULL
SUM({5})    = 5    != NULL + 5 (= NULL)
SUM({5, 3}) = 8    != NULL + 5 + 3 (= NULL)

Is there some obvious advantage of the choice that was made (SUM being NULL) that I have missed?

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).

It's just one of the extremely many inconsistencies in the SQL language.  They make the language harder to teach, harder to learn, harder to understand, harder to use, harder to whatever you want, but that's just the way things are.  The rules cannot be changed "cold" and "just like that", for obvious reasons of backward compatibility (If the ISO committee publishes a final version of the standard, and vendors then set out to implement that standard, then those vendors will not appreciate it very much if in a subsequent version, the rules are changed such that existing (compliant) implementations of the former version of the standard "automatically fail to comply" the new version ...)

(*) It is now better understood that aggregations over an empty set behave more consistently if they systematically return the identity value (= what you call the 'neutral element') of the underlying binary operator at hand.  That underlying binary operator for COUNT and SUM is addition, and its identity value is zero.  For MIN and MAX, that identity value is the highest and lowest value of the type at hand, respectively, if the concerned types are finite.  Cases like averaging, harmonic means, medians, etc. are extremely intricate and exotic in this respect, though.

The main difference I can see is with regard to the datatype. COUNT has a well defined returntype: A whole number. All the others depend on the type of the column/expression they are looking at. Their return type must be compatible with all members of the set (think float, currency, decimal, bcd, timespan, ...). Since there is no set you cannot imply a return type, thus NULL is your best option.

Note: In most cases you could imply a return type from the column type you are looking at, but you can do SUMs not only on columns but on all kinds of things. Implying a return type might get very difficult if not impossible under certain circumstances, especially when you think about possible expansions of the standard (dynamic types come to mind).


T SQL Table Valued Function to Split a Column on commas I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.

Ex: "one,two,three,four" would return a new table with only one column containing the following values:

one
two
three
four

Does this code look error prone to you guys? When I test it with

SELECT * FROM utvf_Split('one,two,three,four',',')

it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.

Here is the function:

USE *myDBname*
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)

RETURNS @SplitValues TABLE
(
    Asset_ID VARCHAR(MAX) NOT NULL
)

AS
BEGIN
            DECLARE @FoundIndex INT
            DECLARE @ReturnValue VARCHAR(MAX)

            SET @FoundIndex = CHARINDEX(@delimiter, @String)

            WHILE (@FoundIndex <> 0)
            BEGIN
                  DECLARE @NextFoundIndex INT
                  SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)
                  SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)
                  SET @FoundIndex = CHARINDEX(@delimiter, @String)
                  INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
            END

            RETURN
END

I wouldn't do this with a loop; there are much better alternatives. By far the best, when you have to split, is CLR, and Adam Machanic's approach is the fastest I've tested.

Next best approach IMHO, if you can't implement CLR, is a numbers table:

SET NOCOUNT ON;

DECLARE @UpperLimit INT = 1000000;

WITH n AS
(
    SELECT
        x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM       sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    CROSS JOIN sys.all_objects AS s3
)
SELECT Number = x
  INTO dbo.Numbers
  FROM n
  WHERE x BETWEEN 1 AND @UpperLimit
  OPTION (MAXDOP 1); -- protecting from Paul White's observation

GO
CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number)
    --WITH (DATA_COMPRESSION = PAGE);
GO

... which allows this function:

CREATE FUNCTION dbo.SplitStrings_Numbers
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
       SELECT Item = SUBSTRING(@List, Number,
         CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
       FROM dbo.Numbers
       WHERE Number <= CONVERT(INT, LEN(@List))
         AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
   );
GO

I believe all of these will perform better than the function you have, when you get it working, especially since they are inline instead of multi-statement. I haven't investigated why yours isn't working, because I don't think it's worth it to get that function working.

But that all said...

Since you are using SQL Server 2008, is there a reason you need to split in the first place? I would rather use a TVP for this:

CREATE TYPE dbo.strings AS TABLE
(
  string NVARCHAR(4000)
);

Now you can accept this as a parameter to your stored procedures, and use the contents just like you would use a TVF:

CREATE PROCEDURE dbo.foo
  @strings dbo.strings READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT Asset_ID = string FROM @strings;
  -- SELECT Asset_ID FROM dbo.utvf_split(@other_param, ',');
END

And you can pass a TVP directly from C# etc. as a DataTable. This will almost certainly outperform any of the solutions above, especially if you are building a comma-separated string in your app specifically so that your stored procedure can call a TVP to split it apart again. For a lot more info on TVPs see Erland Sommarskog's great article.

More recently, I've written a series on splitting strings:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And if you are using SQL Server 2016 or newer (or Azure SQL Database), there is a new STRING_SPLIT function, which I blogged about here:

Performance Surprises and Assumptions : STRING_SPLIT()
STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
STRING_SPLIT() in SQL Server 2016 : Follow-Up #2

CREATE FUNCTION [dbo].[fnSplit]
(

    @sInputList VARCHAR(8000),         -- List of delimited items

    @sDelimiter VARCHAR(8000) = ','    -- delimiter that separates items

)
RETURNS @List TABLE (colData VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)

    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

    BEGIN

        SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX
(@sDelimiter,@sInputList,0)-1))),

        @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)
+LEN(@sDelimiter),LEN(@sInputList))))

        IF LEN(@sItem) > 0
            INSERT INTO @List SELECT @sItem
        END

        IF LEN(@sInputList) > 0
            INSERT INTO @List SELECT @sInputList -- Put the last item in
        RETURN
    END

--TEST

--Example 1: select * from fnSplit('1,22,333,444,,5555,666', ',')

--Example 2: select * from fnSplit('1##22#333##444','##')  --note second colData has embedded #

--Example 3: select * from fnSplit('1 22 333 444  5555 666', ' ')


High CPU usage on SQL server - Slow queries Our MS SQL Server is using about 95% of the CPU-power.

After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how much it is used.

When it's over 80%, every query is extremely slow.

Our website is dealing with alot of big queries, so some of them takes 45-60 seconds.
After a restart (CPU usage less than 80%), it takes 11-20 seconds for the same Query.

How can I fix this? I've read online that affinity masks can adjust the CPU usage, but the Affinity settings are disabled. I cannot change them. Is this because I only have 1 processor?

There are plenty of tricks to do with the queries themselves, but our websites and services are quite big, and there is simply too much to change.

Most of them are already pretty well optimized.

I cannot keep restarting the SQL-Service, even though it only takes 2 seconds, because we have an alarm service that allows people to call in and record a message, a selected group will then be called and hear the recorded message.

This system is used by hundreds Search and Rescue teams, and if the SQL-Service restarts during an alarm, it will terminate and the person that called it in will not be notified.

I have searched all over the place, but found nothing except for stuff about "Affinity Masks", which I cannot change.

There must be a way to clear out the CPU cache, without terminating current queries... right?

SQL: Microsoft SQL Server 11.0.2100.60
OS: Windows Server 2012 x64
Processor: 2.30 GHz
RAM: 4.00 GB

Affinity does not "adjust the CPU usage" (e.g. in your case make the CPUs perform less work), it allows you to either turn off a CPU (perhaps to make it available to another instance on the same machine) or set a CPU to aid with I/O only. Even if you had multiple CPUs, you wouldn't be able to use the former to help with your goal, and it is impossible for us to guess on the latter because we don't know what is driving your CPU usage so high. It could be due to extremely poor indexing, excessive compilations, abundance of scalar UDFs, I/O thrashing, who knows? (And the reason I/O could be the cause is that if your database is larger than 3 GB or so, it will constantly have to swap data in and out of buffer pool memory, and this takes its toll on CPU.)

CPU cache, also, is a rabbit hole you don't need to be going down. I highly doubt your CPU is thrashing at 95% because of problems with your CPU cache.

To help narrow down the source of CPU pressure, and assuming you're using stored procedures, you can take a look at this diagnostic query from Glenn Berry (sourced from here) - make sure you run it in the context of the right database:

-- Top Cached SPs By Total Worker time (SQL Server 2012).
-- Worker time relates to CPU cost  (Query 44) (SP Worker Time)

SELECT TOP (25)
  p.name AS [SP Name],
  qs.total_worker_time AS [TotalWorkerTime],
  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
  qs.execution_count,
  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
    AS [Calls/Second],
  qs.total_elapsed_time,
  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
  qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

If you're not using stored procedures, then this example from John Samson can help isolate ad hoc queries (sourced from here):

SELECT TOP (25)
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
    qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
    (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

You can also take a look at Adam Machanic's sp_WhoIsActive, a stored procedure that can quickly analyze all of the currently running queries, and allow you to sort it however you want (e.g. in your case @sort_order = '[CPU] DESC').

The first thing I would do, though - particularly if this really is mission critical for search and rescue teams - is buy better hardware. You should have more CPUs and more RAM to service your application. You also absolutely need better high availability (e.g. clustering, mirroring or Availability Groups). There is no reason that a reboot of a physical machine should take your application completely offline - we have better solutions for that problem. And finally, I presume this "server" only has one spinny disk drive. This means that all I/O - from the OS, from SQL Server data files, log files, tempdb, etc. all go through a single controller and share read/write activity on a single drive. Get more disks. Get SSDs if/where you can. Use RAID and try to spread the I/O out as much as possible.

That all said, throwing hardware at the problem is not going to be the only part of the fix. You need to isolate exactly what is causing excessive CPU usage and then attack those problems no matter what hardware you're on.

Also see this StackOverflow question for some other ideas:

https://stackoverflow.com/questions/945063/how-do-i-find-out-what-is-hammering-my-sql-server

You should have a caching mechanism in place like memcached to improve the performance


Oracle empty string converts to null When doing an insert, an empty string is converted to null:

insert into test (f) values ('');

Now, there is a row with f containing a null.

But, when I query the table, I cannot use '':

select * from test where f='';
no rows selected

I can use null:

select * from test where f is null;
____F_
NULL

So... it appears that Oracle decided that empty strings cannot be used for insert,  but they remain empty strings when doing queries. Where is the documentation on when an empty string becomes a null and when it remains an empty string?

This says it all:

select NVL('','it is null') as value
from dual;

SQL Fiddle

2 things:

1) '' gets converted to NULL on insert. That's an Oracle VARCHAR2 thing.

2) select * from test where f=''; is trying to do select * from test where f=NULL, which isn't defined, and will return nothing because NULL doesn't like the equality operator. You have to use IS NULL or IS NOT NULL.

I'll add that the CHAR datatype behaves differently because it is padded.

This explains it a little:

create table t1 (c1 char(1),
                 c2 char(3),
                 c3 varchar2(10));

insert into t1 values ('','','');
insert into t1 values ('a','a','a');

declare
v1 char(1);
begin
v1 := '';
insert into t1 values (v1,v1,v1);
end;

select * from t1;

select count(c1) from t1;
select count(c2) from t1;
select count(c3) from t1;

select length(c1), length(c2), length(c3) from t1;


Finding all functions/stored procedures that reference a certain procedure/function? I have been given the task to move reports from SSRS to our internal ASP.Net application and this has involved me re-writing a few functions and a stored procedure to both improve performance, make them output in forms better consumed by the application and make any requested additions to the reports.

What I also need to do is remove the old functions and procedures from the system as I go, before I do this though I want to make sure it will not break anything else. Is there a way I can go though all the precedures and functions to see if any of them contain references to the ones I will be removing?

There are thousands of these to go though and I would rather not have to open each one at a time in SSMS to look. So if there is some table I can query or even if there is a .sql file that SQL server is hiding that I could dig though, that would be great.

sys.sql_expression_dependencies should give you what you want if you don't want to search through the definition of every single object (especially when references may or may not have schema prefixes).

I think you can get what you want with the following query:

SELECT
    o.name AS ReferencingObject,
    sd.referenced_entity_name AS ReferencedObject
FROM sys.sql_expression_dependencies  AS sd
INNER JOIN sys.objects AS o
    ON o.object_id = sd.referencing_id
WHERE sd.referenced_entity_name = 'my_object_name';

This appears to work quite well.
DECLARE @DBs TABLE (
                    DatabaseName NVARCHAR(128),
                    ID INT IDENTITY(1,1)
                    )
DECLARE @Objs TABLE (
                    DatabaseName NVARCHAR(128),
                    Loc NVARCHAR(128),
                    SchemaObjectName NVARCHAR(128),
                    ID INT IDENTITY(1,1)
                    )

DECLARE @LookingFor     NVARCHAR(4000)=N'%SearchValueGoesHere%',
        @intCnt         INT = 1,
        @RecCnt         INT = 0,
        @DatabaseName   NVARCHAR(128),
        @sql            NVARCHAR(max)=N''

INSERT INTO @dbs(DatabaseName)
SELECT
    name
FROM
    sys.databases d
WHERE
    1=1
AND
    d.name NOT IN ('msdb','master','tempdb','model')

SET @recCnt = @@ROWCOUNT

WHILE @intCnt<=@RecCnt
BEGIN
    SELECT
        @databasename = databasename
    FROM
        @dbs
    WHERE
        id = @intCnt
    PRINT @databasename
    SET @sql = N'USE ' +@databasename+ N'
    SELECT
        '''+@DatabaseName+N''' Dbase, ''SQL Modules'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName
    FROM
        sys.sql_modules
    WHERE
        definition LIKE ''' +@LookingFor +N'''
    UNION
    SELECT
        '''+@DatabaseName+N''' Dbase, ''Computed Columns'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName
    FROM
        sys.computed_columns
    WHERE
        definition LIKE ''' +@LookingFor+N'''
    UNION
    SELECT
        '''+@DatabaseName+N''' Dbase, ''Check ConstraINTs'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName
    FROM
        sys.check_constraints
    WHERE
        definition LIKE ''' +@LookingFor+N'''
    UNION
    SELECT
        '''+@DatabaseName+N''' Dbase, ''Default ConstraINTs'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName
    FROM
        sys.default_constraints
    WHERE
        definition LIKE ''' +@LookingFor +N''''

    PRINT @sql

    INSERT INTO @Objs (DatabaseName, Loc, SchemaObjectName)
    EXEC sp_executesql @sql

    SET @intCnt += 1
END

SELECT * FROM @objs


Joining 4 tables and selecting distinct values based on 3 timestamps I have been trying to remove duplicates from my query results based on the time but I would need to consider 3 different times and i am not sure how to do that. As some of the testing happen multiple times, there are duplicates on the part_id and i would only like to have the last tested results from all the 3 tables.

I am running this query now

select lb.request_ky,
lb.build_dm,
lb.part_id,
dv.date_test,
dv.result,
dw.date_test,
dw.result,
tt.date_test,
tt.result

From tableA lb,
tableB dv,
tableC dw,
tableD tt

Where lb.part_id = dv.part_id
And lb.part_id = dw.part_id
And lb.part_id = tt.part_id

This query would give me duplicates on the part id. How can i just get the latest result from each table and join them together?

You have to define first how a "testing" is identified and what "latest" is. Which of the columns identifies a testing and which of the date columns is going to be used for the ordering?

Then, one way to write the query is using window functions:

With cte As
  ( Select
        lb.request_ky,
        lb.build_dm,
        lb.part_id,
        dv.date_test As date_test1,
        dv.result    As result1,
        dw.date_test As date_test2,
        dw.result    As result2,
        tt.date_test As date_test3,
        tt.result    As result3,
        Row_number() Over (Partition By          -- pick a column for the
                             lb.part_id          -- identification
                           Order By              -- and another for the
                             dv.date_test Desc)  -- order
            As rn
    From
        tableA lb
      Join tableB dv
        On lb.part_id = dv.part_id
      Join tableC dw
        On lb.part_id = dw.part_id
      Join tableD tt
        On lb.part_id = tt.part_id
  )
Select
    request_ky,
    build_dm,
    part_id,
    date_test1,
    result1,
    date_test2,
    result2,
    date_test3,
    result3
From
    cte
Where
    rn = 1 ;

Use JOIN with ON condition. Try as like below

Select lb.request_ky,
       lb.build_dm,
       lb.part_id,
       dv.date_test,
       dv.result,
       dw.date_test,
       dw.result,
       tt.date_test,
       tt.result

From tableA lb
     INNER JOIN tableB dv ON lb.part_id = dv.part_id
     INNER JOIN tableC dw ON lb.part_id = dw.part_id
     INNER JOIN tableD tt ON lb.part_id = tt.part_id
Where dv.date_test = dw.date_test
  AND dw.date_test = tt.date_test
  AND dv.date_test = NOW()

All the best.