December 20, 2007 at 5:46 am
We have an application that is trying to update a row in a single table on the database. The table has 54,852,138 rows and is clustered on it's primary key of an identity field. The row size is small 3 ints and a float.
We have two identical copies of the database on the same server (confirmed with SQL Compare) and are using SQL Server Query Notifications to cache data in the C# web front end.
In one data base the transaction completes no problem. In the other it times out. There are no other users on the database or other transactions whilst we do this test.
The stored proc statement being executed is below
UPDATE [VALUES] WITH(ROWLOCK) SET
[value] = @value,
[entity_fiscal_context_id] = @entity_fiscal_context_id,
[account_code_id] = @account_code_id
WHERE
[id] = @id
The puzzling thing is that when we look at the query execution plan on the database that times out it is horrendous and is referencing all the SQl Query notification tables and procedures. In the Db that is successful the plan is as expected with no mention of the SQL notification etc. We've tried getting the server to recompile the plan, droping and recreating the stored proc but it just reverts. I've attached the bad plan to the post as an txt file (just rename to .sqlplan to view in management studio). Can anybody help?
December 20, 2007 at 7:16 am
How often do you update the statistics on these two databases. I'll be they're out of sync. Also, do you do a regular re-index or index defrag? I'll bet there are differences in the index fragementation and the statistics between the two databases. These statistics are what SQL Server Optimizer uses to determine the query plan to use. If everything else is identical, that has to be the difference.
"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
December 20, 2007 at 7:27 am
Grant Fritchey (12/20/2007)
How often do you update the statistics on these two databases. I'll be they're out of sync. Also, do you do a regular re-index or index defrag? I'll bet there are differences in the index fragementation and the statistics between the two databases. These statistics are what SQL Server Optimizer uses to determine the query plan to use. If everything else is identical, that has to be the difference.
There is a job which runs each evening to rebuild statistics and all indexes are rebuilt on a weekly basis in both databases. I just don't understand why in the failing database the execution plan is including the cache notifications and lots of other unrelated tables.
Also checked in sys.dm_db_index_physical_stats and the most fragmented index on the table ine either database is only 0.05% fragmented
December 20, 2007 at 8:07 am
The plan is showing missing statistics on columns. Are you sure auto-update & auto-create statistics are set to on in the bad database?
To get two different plans, something is not identical between these two databases. They're on the same server you so the differences are elsewhere, the data, the structure, the statistics, the database settings. That's where I'd focus. Double check your SQL Compare settings to see that you're not ignoring triggers or something.
"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
December 20, 2007 at 8:35 am
Grant,
Thank you for your help. I've turned off all the 'ignore' options in the SQL compare and still nothing reported, I've rebuilt the statistics on the tables in the bad database and still no change. I've also checked manually in Management studio that these databases have the same properties for everything (including auto stats etc) and they do.
I've attached the plan from the good database so you can see exactly how different this is!
December 20, 2007 at 8:38 am
Are you sure you have exact copies? Data distribution matters as well. Perhaps increase the statistics sampling size?
I'm with Grant. Something has to be different.
December 20, 2007 at 8:51 am
I've updated the statistics using the FullScan option on the table being updated and every table it is related to as well and still no help.
I agree with both of you that something must be different it's just not something that I can see with the tools I have. I'm a developer not a DBA so I'm no expert at all this!
Are there any hidden settings etc for the service broker information?
December 20, 2007 at 9:05 am
I'm not terribly up on the service broker, but a query is a query. If you've got the same query executing on both databases and everything else is equal, you'll get the exact same plan. If you're seeing radically different plans, then something is different.
Can you run profiler to determine that, in fact, both databases are receiving identical query calls?
"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
December 20, 2007 at 9:24 am
I can run through visual studio and pause on the line of code that is making the ado.net call and checked everything is the same.
I've run the profiler and captured the actual sql call and ran both of them in the management studio and it does the same.
I really am baffled. Whether the code is issued in the management studio or from the application the outcome is the same!
December 20, 2007 at 9:37 am
Robert Brown (12/20/2007)
We have two identical copies of the database on the same server (confirmed with SQL Compare) and are using SQL Server Query Notifications to cache data in the C# web front end.
Do you have notification services active on both databases, or just one? If just one, which one?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2007 at 2:12 pm
Notification services are active on both databases. Each has a different Guid for the broker id and the caching itself seems to be working fine when we point at either database.
December 20, 2007 at 11:42 pm
Can you double check that the notification services in the two DBs are set up the same? Same events, same schedules, same subscribers, etc.
I'm not that familiar with notification sevices, but there has to be a reson that one query's hitting the notification tables and one isn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2007 at 1:48 am
Gail,
I'll try to do as you ask but I know I created the broker in the same way in both databases using
ALTER DATABASE FLEXII_GENERATED SET ENABLE_BROKER
followed by these
GRANT CREATE PROCEDURE TO FLEX_USER
GRANT CREATE QUEUE TO FLEX_USER
GRANT CREATE SERVICE TO FLEX_USER
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO FLEX_USER
FLEX_USER has the database owner role and is the user that the application connects with.
This is in a script so was the same for both databases.
December 21, 2007 at 4:51 am
Ok.
As I said, I know very little about notification services. Is there any way to check which query notifications are active in the DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2007 at 7:33 am
if you script out the proc from both databases are the set options the same either side? ansi nulls and quoted identifier changes can cause havoc with the query plans.
are you positive collations are identical too, you'll need to check system databases, server, databases and columns in affected tables.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply