When performance issues rear their pretty little heads I tend to look at the simplest things last. I am the person who is looking at the code first – because in many cases, if your code was better we wouldn’t be having this discussion. Whether it’s a series of nested views or one single user-defined function that doesn’t play well with the optimizer, there is usually something in the code itself that is contributing to the performance issue.
But what about the times when it isn’t the code or you can’t touch the code (I’m looking at you NHibernate…and all of your red-headed step-child offshoots and fellow ORMs). What about the cases when the poor performance situation only occurs periodically, or once a week on the same day, every week, and while you can see the slow-downs in your queries, and all of your waits and plans, the source of the problem isn’t any particular smoking gun – it’s lots of guns, firing off at random times, and the casualties are in your interfaces that depend on the data from the queries.
The one thing I don’t immediately go to is UPDATE STATS – and I should be looking at this. It’s something easy to run and if you still have issues, you have ruled it out.
Over estimation on rows = bad times for the optimizer.
In this case, I ran UPDATE STATS on one table in particular and immediately saw a marked improvement in my test query.
Ahhhh….much better.
This morning I ran the same query and things were back to where they were with performance. This was somewhat disheartening, but the troubleshooting must continue.
But what about auto-update stats? Is this on? Yes, but auto update statistics is only going to update based on a percentage sample of the records in the table. If your table contains millions of records, you may not be updating your statistics sufficiently to see an improvement. I am updating statistics with a FULL SCAN of the table. For larger tables, this may take some time, but you may also see a difference in your execution plan versus only updating statistics based on a sample.
In an effort to rule out whether or not statistics are definitely a factor, I want to UPDATE STATS on all the tables in my query, and at a specific time – sometime the day before we expect our slowdown to occur. I also want to be able to easily see how long the process ran and the duration of the update for each table. I could write all this to a table, and maybe I will do this later, but viewing this from job history is what I want right now – it is easy to look at and easy for everyone else to find and see as well.
Creating a job with multiple steps can sometimes be a bit painful and tedious, if the job is going to have A LOT of steps. With PowerShell, a server name and a database, I can dynamically create this job.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
<# Name: create update stats job for specified tables.ps1 Author: Amy Herold Date: 8 February 2018 Purpose: With a specified server, database and list of tables, create a job to update stats. #> $server = 'SERVER01'; $database = 'MyDatabase'; $job_name = 'DBA - Update Stats on Specific tables'; $tables = @('dbo.Table1' ,'dbo.Table2' ,'dbo.Table3' ,'dbo.Table4' ,'dbo.Table5' ,'dbo.Table6' ,'dbo.Table7' ,'dbo.Table8' ,'dbo.Table9' ,'dbo.Table10' ,'dbo.Table11' ,'dbo.Table12' ,'dbo.Table13' ,'dbo.Table14' ,'dbo.Table15'); $table_count = $tables.Count; $sql_script = "DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'$job_name', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_page=2, @delete_level=0, @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT select @jobId GO EXEC msdb.dbo.sp_add_jobserver @job_name=N'$job_name', @server_name = N'$server' GO`r`n"; for ([int]$i = 0; $i -lt $tables.Count; $i++) { if ($i -lt ($table_count-1)) { $sql_script = $sql_script + "EXEC msdb.dbo.sp_add_jobstep @job_name=N'$job_name', @step_name=N'UPDATE STATISTICS "+$tables[$i]+" WITH FULLSCAN', @step_id="+($i+1)+", @cmdexec_success_code=0, @on_success_action=3, @on_fail_action=2, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'UPDATE STATISTICS "+$tables[$i]+" WITH FULLSCAN', @database_name=N'$database', @flags=0 GO`r`n"; } if ($i -eq ($table_count-1)) { $sql_script = $sql_script + "EXEC msdb.dbo.sp_add_jobstep @job_name=N'$job_name', @step_name=N'UPDATE STATISTICS "+$tables[$i]+" WITH FULLSCAN', @step_id="+($i+1)+", @cmdexec_success_code=0, @on_success_action=1, @on_fail_action=2, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'UPDATE STATISTICS "+$tables[$i]+" WITH FULLSCAN', @database_name=N'$database', @flags=0 GO`r`n"; } } $sql_script = $sql_script + "EXEC msdb.dbo.sp_update_job @job_name=N'$job_name', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_page=2, @delete_level=0, @description=N'', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @notify_email_operator_name=N'', @notify_page_operator_name=N'' GO`r`n"; #$sql_script Invoke-SqlCmd -ServerInstance $server -Database msdb -Query $sql_script; |
The result from this is a job that will update statistics on your list of tables, one at a time. All you have to do from there is give it a schedule.
All those steps…courtesy of the magic that is POWERSHELL!
For this script, I was getting my list of tables based on a specific query that was problematic. If you are doing this for a stored procedure, you can query for the list of dependent tables instead, and instead of hard coding the tables in this code, pass in the name of your stored procedure and get the list that way.
I hope if you have a situation where some one-off stats updates are needed you find this script to be useful. As always, comments and feedback are appreciated. 🙂
Pingback: Quick Update Stats Job – Curated SQL