Sql-server – Poor SQL Server performance when management studio closed

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

From Craig Freedman:

Combining TOP with ORDER BY adds determinism to the set of rows returned. Without the ORDER BY, the set of rows returned depends on the query plan and may even vary from execution to execution.

Always use ORDER BY if you expect a well-defined and consistent order in your result set. Never rely on how your database may store the rows on disk (e.g. via a clustered index) to guarantee a certain ordering of data in your queries.

SQL Server Profiler was designed to do this.

With it you can:

• capture queries as they are executed on the target instance
• capture other instance and database events like deadlocks, logins, and errors
• filter captured queries by text in the query, login name, database name, and so forth