Currently Being Moderated

Do you have a slow running query or under performing table in SAP with SQL Server 2005?

 

When you have a slow running query sometimes it can help to update the SQL statistics on a table.

 

What happens when you run an update statistics on a SQL table is that for the next queries that hit that table it creates a new query plan for that query.

 

This happens because SQL updates the statistics with the default sample and will recompile all statements in the statement cache which reference the update of statistics was referenced to.

 

Before updating statistics it is recommended you run the command "#sp_recompile tablename" command first to see if it is just a plan update that is required. If this command fixes your issue it fixed it because there has been a significant change to the query since when the table was created and that query statement first run against it.

 

If this does not fix it, you will need to run the full update statistics table. The command to run this is:

 

UPDATE STATISTICS ecc.tablename WITH FULLSCAN

 

Note, on a big table this is resource hungry and will take a while to run. Below is a screenshot of a test DB server performance load. Before the query started it was at zero. The query took 11 Minutes to run over 2.4 million records

 

 

updatestatisticsPerf.png

Comments