December 15, 2015 at 1:43 pm
Windows Azure Server: From time to time we get excessive wait times when doing either of the following:
-- drop database
-- restore database
The database itself has a large number of FILESTREAM files, but the issue does not appear to be the database but SQL waiting for something. We have the performance improvements in place of Instant File Initialization. The server is not busy, File I/O is low, the server responds normally when running other queries against other databases. Once the server stops waiting, the server continues the drop / restore as normal.
We were just restoring the database, but getting the excessive wait, so we added a delete before the restore hoping that would clear things up.
We are using premium services on the box; drives, etc ...
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
December 15, 2015 at 6:53 pm
What are the actual wait metrics? Check sys.dm_os_wait_stats before, during, and after the issue. Also, look to sys.dm_exec_requests while the issue is running to see active waits. You need to understand specifically what's causing the waits in order to understand what to do about it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 16, 2015 at 5:11 am
Run sp_whoisactive while doing ANYTHING (i.e. not just drop/restore db) to see what is actually happening from MANY metrics. This includes the ability to get query plans, actual wait metrics, cpu/read/tempdb usage, etc. AMAZINGLY powerful, and I use it every day and make all my clients learn how to use it as well. You can find this awesome freebie from Adam Mechanic on SQLBlog.com
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 16, 2015 at 5:31 am
Yes, sp_whoisactive and sp_who2 are very useful and have already been run. There is no activity on the server. The only action is the drop / restore. It may be some sort of Azure issue.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
December 16, 2015 at 8:52 am
Ahh, Azure. So some external wait perhaps? But still, if a query is just sitting there and not progressing, there should be SOME wait type exposed in sp_whoisactive for the waiting spid.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 16, 2015 at 9:07 am
It could be Azure, but you really should still see a wait of some kind. I'm with Kevin.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply