April 22, 2009 at 8:52 am
I recently updated a lot of views on our production pools. I noticed that the time to update these views differed radically between different server pools. This is the time taken PER DATABASE, not overall for the pool. AND, you have to remember that updating a view does not involve any data manipulation - not even data searching, so the sizes of the databases should have no effect (in fact, each pool has small and large databases, but the time taken was almost the same across all databases on a particular pool). Here are the average times taken to update these views, per database for each of our production pools:
pool avg secs # dbs
------ ---------- -----
prod1 2.48 secs 121
prod2 8.69 secs 418
prod3 10.40 secs 511
prod4 4.23 secs 171
prod6 0.22 secs 9
prod7 1.25 secs 16
prod8 1.18 secs 97
The time reported for prod6 is probably too low. That's because the time reported per database is in integer seconds (the times I list above are the sum of the times reported, divided by the number of databases, rounded to 2 decimal places). prod6 has only databases on it, and most of the times reported were 0. I don't know if the time reported per database is rounded or truncated. So, the times above may be off by as much as 1 full second (if times reported are truncated) or only 0.5 seconds (if times reported are rounded). Either way, the differences are quite dramatic.
Whatever it is that's making these view updates take so much longer on prod2 and prod3 is possibly affecting all queries issued from these servers, which is why I'm concerned with these results. If anyone has any possible explanations to put forward, I'd like to hear them. I don't know why the total number of databases would affect query time per database, but in case anyone wanted the numbers, I've included the number of customer databases for each pool.
Thanks in advance!
April 22, 2009 at 9:44 am
Are you talking about altering a definition? It doesn't pull back data but it does have to validate syntax, check permissions, validate against metadata etc, and more if you are schemabinding. SQL also has to take schema locks to perform the update, and these locks won't be immediately obtainable if the view is in use.
Whatever it is that's making these view updates take so much longer on prod2 and prod3 is possibly affecting all queries issued from these servers, which is why I'm concerned with these results.
What do you mean by this? In the short term any query that uses these views will have to be recompiled.
April 22, 2009 at 11:17 am
matt stockham (4/22/2009)
Whatever it is that's making these view updates take so much longer on prod2 and prod3 is possibly affecting all queries issued from these servers, which is why I'm concerned with these results.
What do you mean by this? In the short term any query that uses these views will have to be recompiled.
Matt, thanks for your reply and help.
Those are the two production pools that are notorious for having the worst performance of our prod pools, probably mostly due to the load as they have the heaviest traffic and the most databases. Our developers think that the conditions that are causing the slowdown of the view definition updates may also be contributing to the generalized performance issues we experience with these two pools. I don't know that I necessarily buy that but thought It would be worthwhile exploring the possibility.
April 22, 2009 at 11:37 am
I wouldn't think so outside of general resource issues. You should take a look here:
It can help you to identify the primary reasons why a server is slower than expected.
April 22, 2009 at 11:46 am
Thanks Matt. Coincidentally I just downloaded the same doc from a different thread. Looks to be an essential read. I just switched from being a developer to DBA about 6 months ago. Have been a programmer/database programmer for a long time though, getting caught up on details of the performance aspects to SQL Server.
April 23, 2009 at 6:22 am
It could be two things causing the slow down, since your views are calling views, the resolution of all those database objects is going to slow things down, as you saw in the other thread. But, you could be experiencing blocking, on two levels. When the view gets created it's going to be blocked by other processes using that view and the underlying views. That blocking will slow things down. I wouldn't be surprised if you ran the update on the view several times during the day or even over a short period you saw different execution times every time.
"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
April 23, 2009 at 7:30 am
Thanks Grant. I have some additional info though. On each of the servers we have a reference database that is identical on all servers. This databae has very little data in it. No customer data etc. just some application list meta data. This database is only used as a reference database for creating new customer databases and has no "load" on it so to speak. The data collected just for this database is consistent with what was posted above...
prod1: 2
prod2: 9
prod3: 11
prod4: 5
prod6: 0
prod7: 2
prod8: 1
So I wouldn't expect the DDL lock or the blocking to be the issue. So far the only correlation is the number of databases on the server and the load on the server. The reference database is not used much but still experiences the same delay with the execution of the DDL statement. Could there be other factors such as the the load on relational engine within the instance that could effect this?
April 23, 2009 at 7:44 am
Ah, well, that's where you'll need to back to the Waits & Queues article that Matt posted to see what's causing the slow down. From the description, it's either CPU or Memory pressure, but you'll want to collect the data to be certain.
"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
April 23, 2009 at 12:15 pm
Thanks Grant, I'm working on it. I'll post what I find out.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply