0

I am querying a DB with a SELECT * on 1 table with less than 1m records. It appears my query has brough the application attached to the DB down.

The DBA has told me to use the correct isolation levels to avoid impact on the db.

I have added WITH (NOLOCK) to the query, will this do? Also, is it normal that 1 query would bring down the db?

user3219693
  • 201
  • 3
  • 20
  • Well, it takes special skills to make query able to shut database down. In most cases, Jedi can do that ;-) OK, seriously, which DBMS you are using? I'd first check if your query has been covered with proper index. Also, if you are reading data (not updating) you can specify NOLOCK, but in most cases you don't have to do that. – Kenan Zahirovic Mar 20 '14 at 13:25
  • The DB is in SQL Server - I think 2008. I am just querying it using Report Builder, I have no access to the table structure so don't know about the index. I just find it odd that a query returning only 5 columns and less than 1m records can cause the app to fall over. what isolation levels should i be using? – user3219693 Mar 20 '14 at 13:29
  • possible duplicate of [Effect of NOLOCK hint in SELECT statements](http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements) – paparazzo Mar 20 '14 at 13:37
  • I have been told that due to blocks that were on the DB it was unable to be used by other people – user3219693 Mar 20 '14 at 13:45

1 Answers1

0

You have a problem with index. It's not a question how many rows you are fetching, you must pay attention on "where" part of your query. If you don't know which indexes exist, then ask your DBA about indexes or send him "where" part of the query. If you are lucky, the right index already exists. If not, you must create new index.

Only if you have right index, you can consider the use of NOLOCK hint.

Kenan Zahirovic
  • 1,587
  • 14
  • 24
  • There was no WHERE part to the query - I just called all data. – user3219693 Mar 20 '14 at 13:44
  • Is there any "ORDER BY" clause in query? Or you are simply exporting data from table without particular order? – Kenan Zahirovic Mar 20 '14 at 14:09
  • No order, just a simple select * from tbl – user3219693 Mar 20 '14 at 14:17
  • OK, since you are fetching complete dataset it's possible that your query takes some time to complete. What exactly is "less than 1 m" records? Is it near a million of records, or much smaller? If you really have, for example 800.000 records... well, it takes same time to get all records. Maybe 20 seconds? Therefore, it's possible that some user has blocked your query. You can try with "SELECT * FROM Yourtable WITH (NOLOCK)" statement, in that case it could help. Or if you can wait for other people to finish their work and try again? – Kenan Zahirovic Mar 20 '14 at 14:54