View will not run from System Databases

  • terry999

    SSCarpal Tunnel

    Points: 4791

    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
    select count(*) from REFDB.ACC.OldAccountView

    if I do:

    use master
    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)
    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?

  • Mr. Brian Gale


    Points: 23053

    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).

  • terry999

    SSCarpal Tunnel

    Points: 4791

    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

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Mike Good


    Points: 7388

    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.


Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply