Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
- sqlserver-dba.com switching from feedburner to follow.it
- Taking transaction log backup and full back at the same time
SQL Server statistics and Datawarehouse
05 February,2011 by Tom Collins
An ETL based process importing data into a Datawarehouse was experiencing some severe SQL Server performance problems . The job normally takes 4 hrs – but on this occasion was still running after 10 hrs. Application support contacted me , to analyse the cause.
Talking to application support – they were aware at which module the problem was occurring. We stopped the job – I set up a SQL trace – and monitored the statements . The first few minutes were OK – and then the job slowed severely on an UPDATE statement . A relatively straightforward statement , which normally takes about 2 minutes. 30 minutes later I was still staring at the screen.
I put the statement in SSMS – did a Display estimated execution plan – and straightway noticed 2 predicates with Index Scan . These indexes are very large ~ 20 million .
As an initial plan I ran UPDATE STATISTICS myTableName MyIndexName on the indexes.
Reran – the Display estimated execution plan - and the Index Scans had changed to Index Seek.
I asked application support to rerun module . This time the UPDATE statement took 2 minutes.
It turned out some new data had been inserted but no statistics refresh had occurred.
The typical workflow of this Datawarehouse is a nightly ETL – and that is the only data changes that occur during the day. Looking at the configurations I noticed the auto update statistics had been turned off . I’ve turned this back on – and apart from the data changes that occur during the ETL – it shouldn’t be triggered .One of the drawbacks of the auto update statistics – is that it will do an update when the SQL Server calculates during the query compilation that the statistics are out of date.
To force the statistics to be updated use sp_updatestats. Read about sp_updatestats at - SQL Server sp_updatestats and UPDATE ... - SQL Server DBA
Read More
SQL Server Performance Killers - SQL Server DBA
SQL Server Performance Checklist - SQL Server DBA
How to report a SQL Server performance problem - SQL Server DBA
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |