May 12, 2017 at 5:54 pm
I migrated my SQL Server database from 2012 to 2016 and I can see 2016 database performance is degraded instead of getting improved. I am using web based application (ASP.NET, C#) with this database. I am facing various issues -
Issue 1 -
My query gives 50,000 records in 2012 database in just 20 seconds, same query takes 26 minutes in SQL server 2016 database. Not sure why ?
facts -
Database is backed up from 2012 server and restored into 2016 server. Exactly same copy.
Data tables/ indexes and all db objects (restored from 2012) are exactly same.
Then what is missing in 2016 database ?
Issue 2 -
I do 2 database operations on this page -
1. Extract data from SQL Server (5000 to 20,000 records)
2. Fill this data in datatable and port this data table to oracle destination table using OracleDataAdapter.
Step 1 code snippet - Fetch data from SQL SErver
SqlCommand cmd = new SqlCommand("Stored_Procedure", conn);
cmd.CommandTimeout = 0;
try
{
da.SelectCommand = cmd;
da.Fill(dtSql);
}
Step 2 code snippet - Update this data into oracle
OracleDataAdapter myDataAdapter = new OracleDataAdapter();
myDataAdapter.SelectCommand = new OracleCommand(OracleQuery, oledbCon);
OracleCommandBuilder cb = new OracleCommandBuilder(myDataAdapter);
DataSet ds = new DataSet();
myDataAdapter.Fill(ds, "LoadedTable");
foreach (DataRow drsql in dtSql.Rows)
{
DataRow dr = ds.Tables["LoadedTable"].NewRow();
dr.ItemArray = drsql.ItemArray;
ds.Tables["LoadedTable"
].Rows.Add(dr);
transtatus = myDataAdapter.Update(ds, "LoadedTable");
}
Here is the issue -
For SQL Server 2016, When my source select query for SQL server fetches number of records till 7000, everything is going well. But if number of records pulled in Step 1 exceed beyond count 18000, page gives command time out error right away.
This never happened with SQL server 2012.
So I tried putting command time out =0.
Issue 3 -
After I fixed the command time out issue by putting command timout to infinity
cmd.CommandTimeout = 0;
my commandtime out exception is gone, but my aspx page goes in waiting mode.. even though database operations are complete in background, looks like it is not updating my page UI.
Generally I get Success message on label on my page, but in this scenario, page remains in Waiting mode only
label does not get refreshed.
My original code has been working in Production with SQL Server 2012 since more than 5 years, it is stable.
The moment i migrated to 2016, above things are happening.
Could anybody advise , where can be the issue ? Code side or 2016 database side ? Is there something which is missing
in 2016 database server configuration ? Does DBA need to check anything on server / database configuration level for 2016?
Fixing Issue 1 may automatically solve Issue 2 and Issue 3
Quick help will be really appreciated.
May 12, 2017 at 9:39 pm
Did you update stats after the upgrade?
May 13, 2017 at 4:35 am
Hi,
You can try various options on the database level, like updating the COMPATIBILITY_LEVEL to 130 since you upgraded from SQL 2012 to 2016, it might still be 110 (ALTER DATABASE <database name> SET COMPATIBILITY_LEVEL = 130).
Also, make sure Auto Create Statistics and Auto Update Statistics are set to 1 (you can check these settings from
SELECT is_auto_create_stats_on, is_auto_update_stats_on FROM sys.databases WHERE name = '<database name>').
If you are still getting issues, I would recommend to revisit the stored procedure that is being executed for performance improvement(s). Please let us know.
Thanks.
May 13, 2017 at 11:12 am
Hi pietlinden & debasis.yours
Appreciate your replies.. I will definitely check with our DBAs if the things you have mentioned have been verified or not..
I will keep you posted....
May 15, 2017 at 8:45 am
This was removed by the editor as SPAM
May 15, 2017 at 9:20 am
This may be due to the new cardinality estimator (CE) that was introduced with SQL Server 2014. Is your database at compatibility level 130? Have a look at the (actual) execution plan for the offending query. Are the estimated row counts wildly different from the actuals? Does the same still happen if you update statistics and try again? I believe there's a trace flag you can specify so that the legacy CE is used - you can try that and see if that makes the query run any faster. If not, perhaps there are some differences in the SET options that are in force at run time?
John
May 15, 2017 at 12:12 pm
Try running update statistics on the tables preferably in an off peak usage period? EXEC sys.sp_MSforeachtable @command1 = N'UPDATE STATISTICS ?', @replacechar = N'?';
May 15, 2017 at 12:18 pm
This was removed by the editor as SPAM
May 17, 2017 at 7:04 am
Hi Guys
First of all, I really appreciate your responses and suggesitons... thanks for that...
I checked with my DBA and asked him all the options you suggested. As per him, all above things are good from database server point of view.
As per John Mitchell-245523 in above response,cardinality estimator works differently for 2016.
My DBA is also saying that due to execution engine changes in 2016, same query may not work exatcly same as of in 2012.
So I asked him then why its running fine in 2012?
So he suggested one way around, but as per him, is is not a good solution for future.
He changed compatibility mode of 2016 server database to 2012 and query worked like charm !!!
So old version of SQL server 2012 works faster than more advanced version of SQL Server which is 2016, strange!!!!
Long term solution he suggested was to redesign my query, that exactly what i dont want to do, because it will take lot
of development, testing efforts and risk of wrong data.
Facts -
The query I use is on 3 tables. Criteria is 1 matching row from 2 tables and 50,000 rows from 3rd table.
Query is selecting total 48 forward columns, out of which 22 columns are derived one (have subqueries), which have complex
case conditions, each case is sometimes refererring to 3rd table as well as other tables with huge number of rows.
I cannot merge all subquery criteria(in 22 columns) to one common where clause, because it will help to select desired
records.
Any thing else which can be done on server level ?
May 17, 2017 at 7:22 am
sarang1183 - Wednesday, May 17, 2017 7:04 AMHi Guys
First of all, I really appreciate your responses and suggesitons... thanks for that...I checked with my DBA and asked him all the options you suggested. As per him, all above things are good from database server point of view.
As per John Mitchell-245523 in above response,cardinality estimator works differently for 2016.
My DBA is also saying that due to execution engine changes in 2016, same query may not work exatcly same as of in 2012.
So I asked him then why its running fine in 2012?
So he suggested one way around, but as per him, is is not a good solution for future.He changed compatibility mode of 2016 server database to 2012 and query worked like charm !!!
So old version of SQL server 2012 works faster than more advanced version of SQL Server which is 2016, strange!!!!
Long term solution he suggested was to redesign my query, that exactly what i dont want to do, because it will take lot
of development, testing efforts and risk of wrong data.Facts -
The query I use is on 3 tables. Criteria is 1 matching row from 2 tables and 50,000 rows from 3rd table.
Query is selecting total 48 forward columns, out of which 22 columns are derived one (have subqueries), which have complex
case conditions, each case is sometimes refererring to 3rd table as well as other tables with huge number of rows.
I cannot merge all subquery criteria(in 22 columns) to one common where clause, because it will help to select desired
records.Any thing else which can be done on server level ?
Can you show this query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 18, 2017 at 12:38 pm
Hi Chris
Thanks for showing interest to understand the query. But unfortunately being SOX audited application, I am not allowed to share any potion of my code / query.
May 18, 2017 at 3:28 pm
sarang1183 - Wednesday, May 17, 2017 7:04 AMSo old version of SQL server 2012 works faster than more advanced version of SQL Server which is 2016,
No!
SQL 2016 is faster in general. The Cardinality estimator (CE) was redesigned for SQL 2014. Most queries either keep the same performance or improve in performance under the new CE. A small percentage degrade. The last few upgrade tests I've done, I've seen ~2% of the queries degrade in performance, however often severely.
Identify all the queries that degrade in SQL 2016, use the Query Store for this. Fix those queries, either by changing the query itself (preferred and strongly recommended), or by adding the hint to the query to force the legacy CE for just that query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2017 at 3:35 pm
sarang1183 - Thursday, May 18, 2017 12:38 PMThanks for showing interest to understand the query. But unfortunately being SOX audited application, I am not allowed to share any potion of my code / query.
You can obfuscate the column and table names, just do it consistently.
p.s. there's nothing in the SOX compliance rules that requires that source code is kept secret. You may have problems taking anything we write here and using it, but disclosure is your own company's rules.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2017 at 8:13 pm
Thanks for your valuable feedback..
I will try to obfuscate the query... It may take time since its too complicated..
48 columns selected.. out of which
22 columns are the derived columns containing a subquery and that and each column's subquery has CASE with at least 4 conditions (WHEN . THEN)
i will post here once I am done....
May 18, 2017 at 9:06 pm
There are SOX rules about sharing data - so don't do it. Like Gail said, source code doesn't fall under the SOX domain.
I'm interested in seeing the query as well. I've found that most things run faster in 2016 than in 2012, but I've not done extensive testing in my own environment yet because others have to get things migrated and processes test first. Then again, like always, it all depends on the structure and the code.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply