We have a sql server database (on azure) with one large table c1billion rows. Each row consists of a key char32 which is an md5 hash and a value int. All queries select multiple values from the table using a key in clause. Sometimes we have 1000+ items in the in clause and the query can be very slow. What are the top things we can do to improve query performance in terms of table structure (partitioning?) and query structure (maybe chunking queries)?
I assume you mean a SQL Server instances hosted in an Azure VM (as opposed to a SQL Azure instance).
BINARY(16)for MD5 hash. Is half the size of
- Must have a clustered index on the hash
- Choose an appropriate hosting plan. Read Premium Storage: High-Performance Storage for Azure Virtual Machine Workloads
- Follow best practices. Read Performance Guidance for SQL Server in Azure Virtual Machines and Performance Best Practices for SQL Server in Azure Virtual Machine
Other than that, approach this as any other performance investigation. Measure. Read How to analyse SQL Server performance. The solution will depend a lot on what your measurements find.