CODEWITHSUNDEEP
sql-serverazureazure-sql-databasequery-performance
CSharped
CSharped

Reputation: 1287

Indexes on a large SQL db

I have a very large table in a azure SQL database which is already having more than 30million rows and has a lot of inserts happening to the table every day ~50-60k

We have various pages in the web app which needs data from this large table... every page has a different way of querying this table in terms of what columns are required and the columns mentioned in the where clause.

Since the Db is on azure, some indexes were automatically applied by azure looking at the queries getting executed which are now causing performance issues as the db size is very large. Using the dmv in SQL I found out that the data size is about 15gbs but the index in almost 65 gb's

How can I create efficient indexes in this case?

Upvotes: 1

Views: 225

Answers (3)

Grant Fritchey
Grant Fritchey

Reputation: 2775

Are you counting the clustered indexes in the index size? Since that index stores the data, it shouldn't count as "index" size. That should only be all the other index types (nonclustered, nonclustered columnstore, etc.).

Creating efficient indexes is always about fundamentals of query tuning. First, identify the queries that are running slow. Second, look at the code and the execution plan to understand what the query is doing and why it might be running slow. More often than not, the problem isn't indexes, but code. Then, depending on what you find in the code and execution plan, fix the code first, and second, create or modify indexes in support of the code. Make sure you're measuring the query performance before changes and after to ensure the changes result in improvements.

Also, Azure SQL Database does no statistics maintenance. You have to set this up. Some of your statistics may need special loving and care. You'll need to look at them in some detail to identify the ones that are aging without update or the ones that need a full scan update versus the ones that should be sampled (and the sample size).

In short, performance tuning Azure SQL Database is basically the same as performance tuning any other SQL Server database.

Upvotes: 0

Alberto Morillo
Alberto Morillo

Reputation: 15618

Indexes created by Automatic Tuning do not make your database show poor performance, however not defragmenting your indexes regularly and not updating database statistics regularly surely can contribute to poor performance. Additionally, index fragmentation can increase database size as explained here, which is one of the symptoms you mentioned above. Please implement a maintenance task as explained here.

Upvotes: 1

StepUp
StepUp

Reputation: 38094

Brentozar publiched their free script which does good analysis such as:

  • which indexes exist
  • how often indexes are used
  • how often the query engine is looking for an index that doesn't exist.

It is possible to conclude which indexes should be dropped at Azure SQL Database based on the above script.

As MSDN says it is possible to drop indexes at Azure SQL Database.

Upvotes: 2

Related Questions