March 2, 2022 at 5:22 pm
Hello,
I have very little experience with Azure DBs. I'm finding myself in need of having to maintain one such DB, and I could really use some guidance.
The Azure DB pertains to an application that has begun to timeout frequently. I ran a query to check for index fragmentation and am seeing that fragmentation is very high across most of the tables. All I want to do is rebuild indexes.
When I connect to this database through SQL Server Management Studio using the server admin login defined in the Azure Portal, I find that my options in general are quite limited, nothing like I would usually see as a db_owner.
When I run the T-SQL to rebuild indexes on a table, I get this error:
"Cannot find the object [mytable] because it does not exist or you do not have permissions."
I'm unable to right-click the indexes and rebuild through the UI either. Nor can I add this login to any database roles via T-SQL. It seems that this server admin login doesn't have the permissions it needs.
Does this need to be set up in the Azure Portal? Where would I find it?
FIXED: This was a T-SQL error, not a permissions issue.
March 2, 2022 at 6:08 pm
I would probably not bother to rebuild indexes, I would suggest updating statistics.
I've set up a Runbook and execute stored procedures that are in place in all of the databases that update statistics.
I've never rebuilt indexes in three years of having databases in Azure
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 2, 2022 at 6:45 pm
It looks like the login did have the permissions it needed, and that the issue was related to the T-SQL I was trying to use to rebuild the indexes.
Michael, does your opinion on not running indexes apply to all SQL Server databases, or just Azure ones? I'd be curious to hear your reasoning.
March 2, 2022 at 9:10 pm
It looks like the login did have the permissions it needed, and that the issue was related to the T-SQL I was trying to use to rebuild the indexes.
Michael, does your opinion on not running indexes apply to all SQL Server databases, or just Azure ones? I'd be curious to hear your reasoning.
There are few absolutes, so saying reindex or don't reindex would be a mistake.
In your case, the fact that your system has got slower and slower doesn't indicate an index issue. You would need to do some research, and gather statistics, to make the best determination as to what will cover your needs in the best manner.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 3, 2022 at 2:45 pm
Piling on.
Sounds like statistics are getting out of date. Focus there, not on index rebuilds (although, those will also update statistics).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2022 at 6:49 pm
I appreciate the feedback.
One reason I've used index rebuilds in the past is because some of the third-party apps we rely on can sometimes end up with a query that generally performs well, but may suddenly become extremely slow, suggesting a bad execution plan may have been cached. My understanding is that rebuilding the index is a somewhat indirect way of invalidating a bad execution plan, and that updating statistics isn't guaranteed to invalidate execution plans. Acknowledging that there are no absolutes, would it be fair to say that in the case of a query sharply declining in performance due to a poor execution plan, updating stats would be the first thing you would look at, assuming this is off hours?
March 3, 2022 at 7:52 pm
I appreciate the feedback.
One reason I've used index rebuilds in the past is because some of the third-party apps we rely on can sometimes end up with a query that generally performs well, but may suddenly become extremely slow, suggesting a bad execution plan may have been cached. My understanding is that rebuilding the index is a somewhat indirect way of invalidating a bad execution plan, and that updating statistics isn't guaranteed to invalidate execution plans. Acknowledging that there are no absolutes, would it be fair to say that in the case of a query sharply declining in performance due to a poor execution plan, updating stats would be the first thing you would look at, assuming this is off hours?
If it was a single procedure, or only a few procedures, I would determine if the plan in the cache is a less than optimal plan. If that's the case, then the procedure(s) need to be examined.
If the performance degradation is across the board, then I would look at updating statistics.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 3, 2022 at 8:19 pm
If it was a single procedure, or only a few procedures, I would determine if the plan in the cache is a less than optimal plan. If that's the case, then the procedure(s) need to be examined.
Right, but the stored procedure is part of a third-party app, then it isn't our stored procedure, and we aren't going to want to make modifications to it unless absolutely necessary. Other than creating a ticket with the vendor to fix their code, what other options are there for addressing these bad execution plans?
March 4, 2022 at 1:59 pm
I appreciate the feedback.
One reason I've used index rebuilds in the past is because some of the third-party apps we rely on can sometimes end up with a query that generally performs well, but may suddenly become extremely slow, suggesting a bad execution plan may have been cached. My understanding is that rebuilding the index is a somewhat indirect way of invalidating a bad execution plan, and that updating statistics isn't guaranteed to invalidate execution plans. Acknowledging that there are no absolutes, would it be fair to say that in the case of a query sharply declining in performance due to a poor execution plan, updating stats would be the first thing you would look at, assuming this is off hours?
Even if it wasn't off hours, updating the stats would generally be better than rebuilding the index. One is very lightweight operation, the other is exactly the opposite. I'd lean heavily on the stats updates over index rebuilds. Also, assuming the stats actually update (and just because you called for it, doesn't mean they will, if no data has changed in the table since the last stats update, none is performed) you will always see the plan get regenerated. Now, it could still come up wonky based on bad parameter sniffing, statistics skew, etc.. But you should always see a new plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply