This question already has an answer here:
I migrated a database from one instance on a server to another. The structure is the same, the data is the same.
BUT ... the queries are going at much different speeds. A variety of queries; not just one.
What can be the potential reasons why? And how to check them?
The query text and the data are the same - so that isn't it. BUT - my previous tables only had 1-2 non-clusted indexes on SQL server. It's POSSIBLE that the data 'accidentally' was originally written in sequential order (good luck organization) - not sure if that's a thing. And then 'rewritten' in random order when I migrated it.
That's obviously the point of clustered indices ... the data can be physically ordered in a precisely commanded way. I have added such clustered indices on an important naturally incrementing column, but ... still ... queries taking 3-5x longer (huge amount).
My hunch is ... somehow .. perhaps the resources/ physical HARDWARE of the server/ or allocated to the database exactly (this is MS SQL Server, again) can be causing the issue, right?
How exactly would I investigate that?
Sadly, I am not the DBA. I'm not that trusting of our DBA either -- he seems a bit ... clumsy in some regards. He tells me the resources are allocated as needed but yeah -- that may be BS.
Anyway again, only differences I can think of are ... physical hardware ... and without clustered indices on the original tables, maybe there was some "good luck" optimization, but I have doubts about that. New clustered indices don't seem to help much.
Is there anything else I could be missing? Is there a checklist I should go down?
We're talking extreme differences.
I will try Showplan but unfortunately I can only use that on the Old Database --- in the new one, the DBA has locked me out of that for 'security reasons' (he will be leaving the company soon :| ) .... but yeah. What would you suggest, assuming that I can at some point get access to the necessary diagnostic tools?