My SQL Server database is 0.8TB and I am running into some performance issues. In particular, I have some very large tables containing big chunks of XML and JSON data.
When viewing data in these tables, I keep getting timeouts. The main problem seems to be that when selecting a single record for some reason it always loads the entire table. From the debug:
0.015: SELECT * FROM [dbo].[API_quote_requests]
0.031: SELECT COUNT(*) FROM [dbo].[API_quote_requests]
Please, can anyone help me to understand why it is doing a "SELECT * FROM" and how I can fix this?
Also, best practise would be to do the following to avoid transaction locks:
SELECT COUNT(*) FROM [Table] WITH (NOLOCK)
...and it would be great to understand if I can meddle with the templates for SQL queries in some way.
Thanks in advance.