0

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?

marked as duplicate by LowlyDBA, Erik Darling sql-server Sep 28 '18 at 22:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.