1
votes

I have a general query regarding table statistics in SQL Server.

Context:
In a project that I am working on, I have several databases that are being used - each contains tables with their respective indexes and constraints. So I was carrying out several test cases and I turned the SQL Server Profiler to create traces so that I can use them with the Tuning Advisor to generate recommendations. The only recommendations that the Advisor has generated for me were a bunch of statistics.

After creating these statistics, I tested out the stored procedures with statistics io set to on, and I noticed a small improvement on the logical reads.

Question:
My question is this: having inserted many statistics that the Advisor generated for me (about 3-4 per table), how do these statistics affect performance on INSERT, UPDATE or DELETE?

1

1 Answers

0
votes

If there are clustered indexes, then any insert/update/delete needs to do an index based operation. This means that statistics could be used to improve the query engine's ability to determine how to insert/update/delete rows.

I cannot say for certain that they are, but it's possible for them to improve performance.

An issue, however, is that the statistics may not be getting updated when many small updates to the table occur; it needs for than a certain number of rows before it automatically updates them. You many want to check this out.