July 9, 2020 at 10:48 am
I'm planning an upgrade to SQL 2017 standard edition from 2008 workgroup Edition. On a new Server with new OS.
I'm restoring the DBs to the new server and keeping their compatibility levels (100). After restore I'm updating Statistics.
The queries run slower on the new server. The new Server is not live so I'm the only one using it
While trying to get an idea of performance I'm running a v.complex view that takes a long time on Both Servers. This hangs:
use master
go
select count(*) from REFDB.ACC.OldAccountView
if I do:
use master
go
select * from REFDB.ACC.OldAccountView
It returns 420 rows out of 800+ and then just hangs. Always stops at row 420.
However if I change it to
use REFDB (or any userDB)
go
select count(*) from REFDB.ACC.OldAccountView
it works.
Changing it to run on any System DB e.g. use msdb it hangs. (System DBs are 140 compatability)
I copied the code from the view into a Stored Proc and it worked regardless of where it ran from.
What is going on here?
July 9, 2020 at 4:56 pm
That is odd that it is only system databases that are causing the slowness. My first thought would be to get an estimated execution plan on the system databases and an estimated and actual on the user databases and compare.
Next, since it is not a live server, run the query from a system database and capture the actual execution plan but once it appears to hang, look into what is causing it to hang. Is there blocking? What is the status of your query? What is the most recent wait type? If there is no blocking, it might not be "hung" as you suggest, but might be spilling to tempdb and just running long. If after checking for blocking you find nothing, I'd try letting it run for a long period of time and compare execution plans.
That being said, I never run commands like that from a system database. At worst, it will be from a user database as I am doing a query that needs to touch data from 2 databases. System databases I keep out of for the most part. Only going in when I need to do some system related thing (review logins for example).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 9, 2020 at 5:45 pm
Thanks Brian
The view is working now!! regardless of where its being used from. Sorry if I wasted your time
I never run queries from systemdbs, this was just laziness on my part, not wanting to write out
use REFDB
So I just prefixed it with DB name.
Case of getting side tracked into why this wasn't working. It was a query I picked just because I knew it was slow so I get some time stats. I should have been running a real workload.
When it was hanging I did check activity monitor no blocking only processes where the query and some standard SQL Agent tasks. No blocking or many resources if any at all used. Logs of SQL Server and OS logs showed nothing.
I did switch on Query Store on one of the DBs this query hits. I'm due to test another data move from scratch. If it happens again I'll repost using your suggestions - execution plans.
Don't like issues that just "disappear" as they have a habit of reappearing
July 9, 2020 at 7:27 pm
A new cardinality estimator was introduced in SQL Server 2014 and could cause some queries to get a less than optimal execution plan. I suspect that is what is happening here - since running from a system database would utilize the new feature and running directly from the database (which is in a lower compatibility mode) would not use the new feature.
If you plan on changing the compatibility mode then you should also look at query store so you can capture those queries that have issues and address them as needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2020 at 4:34 pm
One thing to keep in mind is that the current DB context specifies whether PARAMETERIZATION is FORCED or SIMPLE. When FORCED, some filtered indexes may be rendered unusable.
ref: https://www.sqlservercentral.com/articles/Indexing/129423/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply